View previous topic :: View next topic 
Author 
Message 
philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Fri Apr 29, 2011 1:11 am Post subject: SOLVED Ignoring blank cells 


If I start in C10, is there a way of ignoring all the empty cells between C10 & say C15. When it gets to C15 do a percentage calculation for the difference between the two cells C10 & C15 & show the result in C5. Once it's done that, the formula starts again from C15 & looks for the next cell that's got data, calculates & shows the result of C15 & C?? in C5 This will show the price change between shopping trips. The reason for the blank cells is, you don't buy everything on the list every week I also want to show the overall percentage change from the first cell C10 to whatever the last cell C?? is at that time in C6. I've got multiple columns, but only want to do it on a column basis.
I'm trying IF(OR & IF(AND formulas because that's the limit of my expertise. I thought somebody is bound to know a better way.
I'm fed up with going to the supermarket & watching their prices vary as wildly as they do. So I was thinking I'll track they're price changes.
Yes I know I've got too much time on my hands. I have an excuse. I'm waiting for a govt dept to do something. Then I can do what I want
Last edited by philb1 on Wed May 04, 2011 11:29 pm; edited 1 time in total 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Fri Apr 29, 2011 5:45 am Post subject: 


Not sure about your sheet structure.
Attached doc (Percentage Price changes.ods) has blanks and dollar values in B10:B35.
Array formula in B5 calculates the change in price between the first and second dollar amount divided by the first dollar amount.
Array formulae must be entered using Ctrl+Shift+Enter PC key combination.
When the B5 array formula is filled down into B6 (the Ctrl key must be held down while the fill handle is dragged) it returns the change in price between the second dollar and third dollar amount divided by the second dollar amount; and so on down the column.
The array formula in D5:D8 calculates the overall change in price.
http://www.mediafire.com/view/?mxdhjlgb0z7bnka
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Fri Oct 05, 2012 5:49 am; edited 1 time in total 

Back to top 


philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Fri Apr 29, 2011 9:18 pm Post subject: 


Thanks Ken.. It does mostly what I want. It's much more advanced than where I'm at right now lol. I'm trying to get my head around how the SMALL & ROW functions work, so I can see how it works. It look like the formula's repeated 3 times with  & / to give the result. It might take me a while 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Fri Apr 29, 2011 10:26 pm Post subject: 


This might help you understand the array formulae...
Array enter (Ctrl+Shift+Enter) this formula into any spare cell on Sheet1...
Code:  IF($B$10:$B$65<>"";ROW($B$10:$B$65);"")  [copy/paste the formula text then add the equals sign then press Ctrl+Shift+Enter. When a complete array formula is copy/pasted from here into a Calc cell it is automatically incorrectly entered.]
You should get an array of row numbers and blanks. Each row number is the row number of a column B cell with a dollar amount.
The SMALL function is used to extract each of those row numbers in order from smallest to largest.
To extract the smallest try array entering this formula into another Sheet1 cell...
Code:  SMALL(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"");1) 
The other row numbers are extracted by simply changing the SMALL function's 2nd parameter from 1 to either 2, 3, 4 or 5. If you go past 5 you will get the #VALUE! error because the array returned by IF($B$10:$B$65<>"";ROW($B$10:$B$65);"") has only 5 numbers.
Another way of extracting the row numbers is to use the ROW function to generate the numbers 1 to 5.
Try array entering this formula into a Sheet1 column F cell...
Code:  SMALL(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"");ROW(F1)) 
This should return the smallest row number from the array returned by IF($B$10:$B$65<>"";ROW($B$10:$B$65);"").
To get the other row numbers hold down the Ctrl key while you drag the last formula's fill handle down four more cells. When the formula is filled down the column the ROW(F1) part increments to ROW(F2), ROW(F3), ROW(F4) and ROW(F5). If you filled down any further you would get the #VALUE! error because ROW(F6) etc exceeds the number of values that can be extracted.
These extracted row numbers are used as the INDEX function's 2nd parameter so that the dollar values in column B can be extracted and used in the percentage calculations.
Ken Johnson 

Back to top 


philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Sun May 01, 2011 10:10 pm Post subject: 


Thanks Ken.. That is a very good explanation. You should have been a teacher.
Is there a way of having the overall % change result to display in the same cell after each calculation? I was hoping to do that with the other incremental % change too. After looking at your formula the way I see it, the answer is no. I could be wrong. I usually am ;) 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Mon May 02, 2011 1:27 am Post subject: 


philb1 wrote:  Thanks Ken.. That is a very good explanation. You should have been a teacher. 
Check out my profile
philb1 wrote:  Is there a way of having the overall % change result to display in the same cell after each calculation? 
See if the array formula in D5 in the attached doc (Percentage Price changes2.ods) does what you are wanting.
philb1 wrote:  I was hoping to do that with the other incremental % change too. After looking at your formula the way I see it, the answer is no. I could be wrong. I usually am 
Not sure I understand this one but see if the array formula in F5 is OK.
http://www.mediafire.com/view/?aw01o595353slbb
After a second look at the formulae I suggested in response to your first post I have noticed a simpler way of achieving the same results.
It's a bit embarassing that I used a formula in the form of (AB)/B instead of the simpler form A/B1. With array formulae the parts represented by the A and B are long and complex so using the simpler form significantly reduces their length.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Last edited by ken johnson on Fri Oct 05, 2012 5:51 am; edited 1 time in total 

Back to top 


philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Mon May 02, 2011 1:42 am Post subject: 


As you're just the other side of the ditch, you can probably hear the grinding noises as I try to get my brain into gear lol. I'll have a study & see if I can figure out how it works..
Thank you 

Back to top 


philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Mon May 02, 2011 3:03 am Post subject: 


If count B10B65 is less that 2, do nothing, otherwise index (find) any entries from B1B65536, look for the last number between B10B65 providing it's not nothing & then reference to which row & cell it's in,divide that by doing the same again but look for number before & then minus 1.
I can't see how it looks for the the last number & the one before it. If MAX looks for the biggest & SMALL looks for the smallest. Something to do with rank? I can see the calculation, sort of, although it would take me a month of Sundays to come up with it
Code:  IF(COUNT($B$10:$B$65)<2;"";INDEX($B$1:$B$65536;MAX(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"")))/INDEX($B$1:$B$65536;SMALL(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"");1))1) 


Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Mon May 02, 2011 5:58 am Post subject: 


philb1 wrote:  I can't see how it looks for the the last number & the one before it. If MAX looks for the biggest & SMALL looks for the smallest. Something to do with rank? 
I think an important thing to emphasise is the nature of the numbers that the MAX, SMALL and LARGE functions are analysing. The numbers in the array returned by IF($B$10:$B$65<>"";ROW($B$10:$B$65);"") are row numbers, not prices. The maximum of the array of row numbers is returned by MAX(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"")) and is the row number of the column B cell holding the most recent price [could have used LARGE(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"");1) instead of the MAX function].
The row number of the column B cell holding the next most recent price is the second largest number in the array and that number is returned by LARGE(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"");2)
In... Code:  IF(COUNT($B$10:$B$65)<2;"";INDEX($B$1:$B$65536;MAX(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"")))/INDEX($B$1:$B$65536;SMALL(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"");1))1)  I should have used the MIN function instead of SMALL... Code:  IF(COUNT($B$10:$B$65)<2;"";INDEX($B$1:$B$65536;MAX(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"")))/INDEX($B$1:$B$65536;MIN(IF($B$10:$B$65<>"";ROW($B$10:$B$65);"")))1)  is a tad shorter and clearer.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Wed May 04, 2011 11:28 pm Post subject: 


Thanks Ken. I wish there were teachers like you when I was at school many moons ago. I would have taken notice rather that cause havoc :lol:
I've worked out how they work too
I'll put them in my folder with the others, for use another day..
Cheers.... Phil 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Wed May 04, 2011 11:45 pm Post subject: 


You're welcome Phil.
Was that tornado anywhere near you?
Sure hope not.
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


philb1 OOo Enthusiast
Joined: 21 Mar 2011 Posts: 111 Location: Auckland

Posted: Wed May 04, 2011 11:53 pm Post subject: 


No that was on the north shore (the other side of the harbour bridge to us) My youngest son was driving when it hit. He says there were roofs being ripped off houses, a trampoline flew straight across the front of his car, apparently there was all sorts flying & he was in the middle of it. Luckily he came through unscathed & so did his car 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Thu May 05, 2011 1:17 am Post subject: 


Wow!
That would be very frightening. 

Back to top 


