[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 Blank cells cause problem with formula Goto page 1, 2  Next
Author Message
tinstaafl
General User

Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

 Posted: Thu Oct 19, 2006 1:00 pm    Post subject: Blank cells cause problem with formula I have a rather simple spreadsheet that contains the formula: IF(ISBLANK(C28);"";(C28-C27)/B28 The problem is that sometimes C27 may be blank, in which case I want (C28-Cxx) where xx is the first row above C28 that contains a value -- which may be several rows above -- say C25 AND in that case, I don't want to divide by B28, but rather by SUM(B26:B28) I need and would appreciate some help with the needed formulas. Thanks, Phil_________________Traveling the highways of America since February 2000 (http://www.stringbean.com)
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

Posted: Thu Oct 19, 2006 6:07 pm    Post subject:

I got this working, but it's complicated. If anyone has a simpler/better solution, please post it here.

First we need a running total of the figures in col B (see cry for help at end of post). I used col K. In K1 enter
 Code: =SUM(\$B\$1:B1)
and fill down as far as needed.

To make the formulae a bit more manageable, first define a name for a "subformula"
• Select the cell on row 28 in which you are going to be performing your calculations.
• Insert > Names > Define
• In the Name box type  Code: LastRow

• In the Assigned to box enter Code: MAX(NOT(ISBLANK(\$C\$1:\$C27))*ROW(\$C\$1:\$C27))
(No "=" sign)
• Click OK
We will be using this to find the row number of the last non-blank row in column C

Now enter the formula (in row 28)
 Code: =IF(C28="";"";(C28-OFFSET(\$C\$1;LastRow-1;0;1;1)/(K28-OFFSET(\$K\$1;LastRow-1;0;1;1))))
and finish editing with Ctrl+Shift+Enter (NOT Enter) to make it an array formula. The formula will then look like
{=IF(C28="";"";(C28-OFFSET(\$C\$1;LastRow-1;0;1;1)/(K28-OFFSET(\$K\$1;LastRow-1;0;1;1))))}
(Don't type in the braces - Calc puts them there for you.)
(Column K is your running total of column B.)

Make sure this formula is returning the results you need. If I haven't understood your post correctly I could be out by a row or two on the totals.

===Edit===
Forgot to add: Do not Fill this formula up/down the column. Relative addresses in array formulae do not always increment as you might expect when you fill. Use Copy and Paste
===end Edit===

+ + + + +

Thanks to whoever posted this method in a previous thread (I can't find it just now - and don't remember which of the gurus it was...).

+ + + + +

Cry for help:

If any of you wizards have the time and inclination...

I originally tried to do this without the extra column for a running total. However, I expect
 Code: {=SUM(OFFSET(\$B\$1;LastRow-1;0;ROW()-LastRow+1;1))}
to return the sum of col B since the last non-blank entry in col C. What I get is the value in col B next to the last non-blank entry in col C.

Can anyone see what I'm doing wrong?
(SUM seems to work as an array formula...)
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
tinstaafl
General User

Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

 Posted: Thu Oct 19, 2006 7:39 pm    Post subject: In case it helps I have posted the "in progress" spreadsheet at: http://www.stringbean.com/GasolineMilage.ods_________________Traveling the highways of America since February 2000 (http://www.stringbean.com)
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Fri Oct 20, 2006 12:19 am    Post subject: Dale, the following may be the post you want. I'm too tired to deal with this sort of complexity at present (if not always): http://www.oooforum.org/forum/viewtopic.phtml?p=130414#130414_________________ search forum by month
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Fri Oct 20, 2006 12:58 am    Post subject: It certainly doesn't seem easy, and I suspect my solution is more complex than necessary, but anyway... I put a couple of extra columns, one to increment at each odometer reading, and another to total the cumulative gallons. Then I used a VLOOKUP() to find the previous odometer reading and also the previous gallons cumulative total, so they could be subtracted from the current totals. Cell H10 looks a bit odd, so I suspect there may be some glitch somewhere, or maybe you didn't completely fill the tank? I really should go and do some work.... It's at http://www.mytempdir.com/1003516
tinstaafl
General User

Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

 Posted: Fri Oct 20, 2006 2:05 am    Post subject: Where there is no odometer reading it's because I did not fill the tank (figured odometer reading not relevant if tank not filled). Column J doesn't work where there is no value in Column E of the preceeding row. It's the same problem as in my original post. Involves (E24-E23) where there is no value for E23. I should be able to fix that using your solution for the original column F. First I have to have my coffee, wake up, and study your solution. Thanks_________________Traveling the highways of America since February 2000 (http://www.stringbean.com)
tinstaafl
General User

Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

Posted: Fri Oct 20, 2006 2:25 am    Post subject:

 Quote: Where there is no odometer reading it's because I did not fill the tank (figured odometer reading not relevant if tank not filled).

That's true, but it looks as if there are a number instances where the tank was not filled but the odometer reading was recorded anyway.
_________________
Traveling the highways of America since February 2000 (http://www.stringbean.com)
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Fri Oct 20, 2006 6:27 am    Post subject:

 tinstaafl wrote: In case it helps I have posted the "in progress" spreadsheet at: http://www.stringbean.com/GasolineMilage.ods

Try this:

Create a new column D.
In D4 have =C4
In D5 have =IF(C5="";D4;C5)

Copy down column D. What this will do is to replace any empty cells as in C with the prior value, and repeat until a new value is seen.

Use column D in your calculations, not column C.

EDIT: Still some other considerations, but I have chores. Will look later if no-one else beats me to it.

David.
tinstaafl
General User

Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

 Posted: Fri Oct 20, 2006 10:13 am    Post subject: Dale -- I'm going to have to spend some time on your solution before I can follow it. I'm afraid that I'm a long way from where you are. Rick -- I'm reading the help material on VLOOKUP(). It doesn't appear too complicated but I tend to lose my footing in arrays. David -- Having created the new D column, a new problem developes. For example, the formula in G28 (mpg) correctly calculates the total milage since the last fill-up, but then divides by the number of gallons just purchased. It ignores the gallons added from A26 and A27. As an open inquiry, can an IF function be "nested" inside another IF function? For example: IF(test;then;IF(test;then;otherwise))_________________Traveling the highways of America since February 2000 (http://www.stringbean.com)
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Fri Oct 20, 2006 10:27 am    Post subject: Nested IF's are fine, but be careful of the brackets, etc., and don't put lots of IF's inside each other. VLOOKUP might be better called VLOOKDOWN, because it looks down a Vertical column until it finds what you told it, then gives the result on that row from the column you tell it. In cell H10 in the file I posted, the first VLOOKUP is looking for 4 (B10-1), starting at cell \$B\$4, looks down that column, then gives the answer in the 4th column of columns B to E. Is this any help? It's difficult to explain things I use as second nature.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Fri Oct 20, 2006 10:41 am    Post subject:

 tinstaafl wrote: David -- Having created the new D column, a new problem developes. As an open inquiry, can an IF function be "nested" inside another IF function? For example: IF(test;then;IF(test;then;otherwise))

Sorry, I won't have time to consider this for a while. I realised as I was offering the option that here would be other things to consider. My own would be to rethink the entire thing. There are some really good people here in the meanstime who are more talented than I am [I might solve a lot of problems, but they are all little ones.]

So, for now, good luck and sayonara. I am trying to deal with a computer virus, so it might be a while, especially if I have to redo the computer from ground zero. I'll take a peek back in then.

David.
tinstaafl
General User

Joined: 13 Sep 2006
Posts: 43
Location: America's Highways

Posted: Fri Oct 20, 2006 10:59 am    Post subject:

Rick --

 Quote: In cell H10 in the file I posted, the first VLOOKUP is looking for 4 (B10-1), starting at cell \$B\$4, looks down that column, then gives the answer in the 4th column of columns B to E.

If you could elaborate on that statement I think I could get it. I don't understand what you mean by:
 Quote: . . .looking for 4 (B10-1)

I also don't follow:
 Quote: . . . then gives the answer in the 4th column of columns B to E.
(even if you meant 4th row, I don't see why anything would go to all columns B through E.

Sorry I'm not getting this. I know that you guys have other things to be doing.
_________________
Traveling the highways of America since February 2000 (http://www.stringbean.com)
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Fri Oct 20, 2006 11:14 am    Post subject: VLOOKUP has 4 parts: =VLOOKUP( first part ; second part ; third part ; fourth part ) the first part is what you're LOOKing for the second part is the range where it should look (and it always LOOKs down the left column of this range) the third part is which column to use for the result the fourth part is whether it should look for an exact match or the first close match It's probably best explained with an example. I'll post one later or tomorrow. Beer and dinner now!
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Fri Oct 20, 2006 9:58 pm    Post subject:
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

Posted: Sun Oct 22, 2006 5:49 pm    Post subject:

 noranthon wrote: Dale, the following may be the post you want. I'm too tired to deal with this sort of complexity at present (if not always): http://www.oooforum.org/forum/viewtopic.phtml?p=130414#130414
Indeed it is (how do you keep track of all this stuff?). Clever bloke that dfrench...
 tinstaafl wrote: Dale -- .... I'm afraid that I'm a long way from where you are.
I wouldn't bet money on that. This stuff looks a lot harder and more intimidating than it really is. You'll be looking at it one day, have a Eureka moment and wonder why you thought it was all so difficult.
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 HoursGoto page 1, 2  Next Page 1 of 2

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum