OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Blank cells cause problem with formula
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
tinstaafl
General User
General User


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

PostPosted: Thu Oct 19, 2006 1:00 pm    Post subject: Blank cells cause problem with formula Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Thu Oct 19, 2006 6:07 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
tinstaafl
General User
General User


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

PostPosted: Thu Oct 19, 2006 7:39 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Oct 20, 2006 12:19 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Fri Oct 20, 2006 12:58 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
tinstaafl
General User
General User


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

PostPosted: Fri Oct 20, 2006 2:05 am    Post subject: Reply with 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).

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)
Back to top
View user's profile Send private message Visit poster's website
tinstaafl
General User
General User


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

PostPosted: Fri Oct 20, 2006 2:25 am    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Oct 20, 2006 6:27 am    Post subject: Reply with quote

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. Smile

David.
Back to top
View user's profile Send private message
tinstaafl
General User
General User


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

PostPosted: Fri Oct 20, 2006 10:13 am    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Fri Oct 20, 2006 10:27 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Fri Oct 20, 2006 10:41 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
tinstaafl
General User
General User


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

PostPosted: Fri Oct 20, 2006 10:59 am    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message Visit poster's website
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Fri Oct 20, 2006 11:14 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Fri Oct 20, 2006 9:58 pm    Post subject: Reply with quote

See http://www.mytempdir.com/1005605
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Sun Oct 22, 2006 5:49 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group