Howdy, Stranger!
Categories
Options
Excel feckwittery
The materials in this website are in no way intended to replace the professional medical care, advice, diagnosis or treatment of a doctor, qualified personal trainer, therapist, dietitian or nutritionist Runners's World, Part of the Hearst UK well-being network © Copyright 2017
Comments
*drools over excel thread*
*adds to favs*
=IF(ISNA(VLOOKUP(....etc)),"",VLOOKUP(....etc))
So, my lesson for today: dynamic ranges.
You know how you can select a block of data and give it a name? No? well that's a different lesson.
Well these named ranges can be very useful for lots of things - I'll use pivot tables as an example. When asked the question "Where is the data that you want to use?", you can either select some data and it fills out the cell references e.g. 'Sheet 1'!$A$1: $G$100' or you could call that block of data something useful like 'PivotData' and just type 'PivotData' into the Range box.
That's great if your data is always exactly 100 rows and never more or less than 7 columns. But what if you add new rows all the time (or delete some) or if you add more columns that you might want to put in your pivot report? I've seen a lot of examples of incorrect reports because someone did a pivot table based on 100 rows of data when there were actually 150 rows due to things being added after the pivot table was created.
"Aha", you cry, because you like Norweigan 80s pop bands. "I could just select the whole sheet and tell Excel to base my pivot table on thousnads more rows than I actually need". Yes, you could - and you'd find that your file is suddenly massive. And your pivot table will have a field called 'blank'.
The clever solution is a dynamic range. This is a named range that changes as you add or delete data from it. Using the example above of a sheet where we have data in columns A to G and rows 1 to 100, you could select that, go to 'Insert -> Name ->' on the menu, call it PivotData and press OK. But the fiendishly clever bit is to do the above but stop before pressing OK. Then in the 'Refers to:' box at the bottom of the 'Define Name' screen, type this:
=OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1))
Press Add, then OK, and you have a dynamic range. Now if you add another row your PivotData range will expand to include it, and your pivot table will never again be missing data (as long as you remember to refresh it).
It's a neat trick that I also often use for validation lists, e.g. I have a cell on one sheet with a drop down list of options that comes from a range on another sheet. If I make it a dynamic range I can add new options to the list and the drop down will pick them up.
Here endeth today's lesson.
Excel childcare:
=IF(CRYING,IF(SMELLY,CHANGE,FEED),"")
Today, SUMPRODUCT.
Say you have a column of prices and a column of quantites, and you want to calculate the total value. With prices in A1:A10 and quantities in B1:B10:
=SUMPRODUCT(A1:A10,B1:B10)
And bingo, you've got the sum of A1 x B1, A2 x B2 etc in a single cell. What if there are some rows that you don't want to include in your total? Easy, just put 1 in column C against the ones you want to include, 0 against the ones you don't want, and:
=SUMPRODUCT(A1:A10,B1:B10,C1:C10)
*hands SVT another apple*
This is the only place I can say this without getting banished off the forum BUT...
For my tri training plan, I have two worksheets set up that look up the week I am on, the type of training done ie bike, swim or run and then calculates the percentage of time I have (or not) spent running, cycling and swimming. It is all fairly easy sum/if calcs but it works very well
I love it when race results are published in Excel format, particularly tri results. The analysis is endless.
A standard calc is seeing what percentage of time I spent on each discipline compared to the average, to all senior men, other pirates etc. Hours of fun!
<sigh>
Heaven
OMG I just luuurrrrv this thread
I'm an accountant
Curly - Po10?
(Anything that involves Excel and hours of fun sounds good to me.)
I'm quite fond of my weight loss tracker with (no pun intended) weighted average projection of future weight. Looks good on a chart.
Accountant, formerly programmer.
http://www.thepowerof10.info/
Rankings, Running, Results = heaven
There is hours of fun to be had on here then comparing your compariots from a race with their usual times etc...
*cough* Excuse me bursting in like this
I'm filling in a spreadsheet and when I put the telephone number in it loses the first 0, I know its possible to stop this but can't remember how.
Thank you please
I'll pack a few parcels and come back to check in a mo
Thanks Shimmy, I knew that really just use Spreadsheets so rarely
Curly, I did it Shimmy's way but thanks anyway
I'll try out that method next time I do a phone number thing Curly. Not come across that before.
HTH Beebs
I always do the ' for making numbers into text.
To turn a number formatted as text back into a number, just multiply it by 1.
I would like to freeze my top line as it has all the headers. If I highlight the row and then do 'freeze pane' it seems to want to freeze the top 10 lines.
Pwease help.
*puppy dog eyes*
M.ouse - try selecting a cell in row 2 and then freeze panes. That would normally work.
thank you!