Hello everyone;
In the attached spreadsheet, Row-3 gets it values from Row-2 (it's a cumulative total). I need some assistance for a formula statement like this;
If F2, G2....is NULL, then F3,G3....is also NULL
If F2, G2...is a number (including 0), then continue with cumulative total.
Note that row-2 is manually entered. Row 3 relies on row-2 to continue with its total.
Can this be done? Will appreciate any help folks!
Thanks
Last edited by amt7565; 02-08-2014 at 09:08 AM.
Try:
B3:Formula:
=IF(COUNTA(B2:C2)=0,"",N(A3)+N(B2))
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
This is how it would work for B...
=IF(B2="","",B2)
you can use that same principal for teh others...
=IF($B3="","",B3+C2)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@Ford:???Formula:=IF($B3="","",B3+C2)
The formula in C3=B3+C2, so If B3 is null....
But B3 is absolute; $B3
Just the column. It looks like that can be copied across...
=B3+C2
=C3+D2
=D3+E2
So that can be just copied across, with B fixed
But the check for blank will always be against $B3 ...
Maybe see the attached updated workbook.
Regards, TMS
Awesome! This works just like you suggested. Thank you TMS and Ford.
This now brings me to my end case- which is a graph.
In the attached spreadsheet, I am attempting to graph this very solution. The red graph as you can see treats the NULL like a '0' and hence plots it ending in '0'. This is the row-3 that holds the formulas we entered.
The blue graph is from row-2 and does not contain the formulas. It however treats the null as a null, not '0'.
I want the red graph to look like the blue one. i.e; Do not plot '0'. It should just hang.
Is this possible?
Thanks
Trevor...But the check for blank will always be against $B3...
that is correct, if B3 is blank, they will all be blank
Try:
Formula:=IF(COUNTA(B2:C2)=0,NA(),N(A3)+N(B2))
Regards, TMS
I was able to solve my problems.
Thanks everyone for your generous help.
@Ford: I read this:
as meaning if 2 cells in a row are blank, the accumulator row also remains blank, not continue as a flat lineIf F2, G2....is NULL, then F3,G3....is also NULL
If F2, G2...is a number (including 0), then continue with cumulative total.
Can you share with us what you did?I was able to solve my problems.
TMS, I used "=IF(N21="",#N/A,M22+N21)"Originally Posted by TMShucks
#N/A forced the graph to not plot '0'.
The rest is based on your recommendation.
Thanks!
AMT
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks