How do I tell a cell to activate a formula only when a value is greater than zero?

Hi all,

I'm VERY new to Excel and I'm trying to understand how to tell it to activate a formula I have already inputted ONLY when there is a value greater than zero. It's a very simple formula [=SUM(N15:R15)] but due to the nature of a further formula it is vital to, I need it to only activate when there is actually a value put into the row as when it is blank, it is giving a value of 0 meaning that the other formula is giving an incorrect answer.

In case you're wondering, the other formula is supposed to give an ongoing, increasing figure based on the difference between a previous value and the current one over time. A little hard to explain but this is the formula I've created, maybe there's a simpler way to do it?

=SUM(SUM(N4:R4)+(SUM(SUM(N5:R5)-SUM(N4:R4)))+(SUM(SUM(N6:R6)-SUM(N5:R5)))+(SUM(SUM(N7:R7)-SUM(N6:R6)))+(SUM(SUM(N8:R8)-SUM(N7:R7)))+(SUM(SUM(N9:R9)-SUM(N8:R8))))+(SUM(SUM(N10:R10)-SUM(N9:R9)))+(SUM(SUM(N11:R11)-SUM(N10:R10)))+(SUM(SUM(N12:R12)-SUM(N11:R11)))+(SUM(SUM(N13:R13)-SUM(N12:R12)))+(SUM(SUM(N14:R14)-SUM(N13:R13)))+(SUM(SUM(N15:R15)-SUM(N14:R14)))+(SUM(SUM(N16:R16)-SUM(N15:R15)))+(SUM(SUM(N17:R17)-SUM(N16:R16)))+(SUM(SUM(N18:R18)-SUM(N17:R17)))+(SUM(SUM(N19:R19)-SUM(N18:R18)))

If that makes any sense to you, you might understand that any zeros further down will have an adverse affect on that sums outcome so I'm trying to stop the formulas from activating until a value is put in instead of if being blank and defaulting to zero.

I hope this gives a good enough explanation for somebody to be able to help me but I know my information is far from ideal!

Thanks

Not sure if this helps (or works) but I've attached a screenshot along with a link to the image so you should at least see one of them.
Current.jpg

http://www.ilovewhat.co.uk/random/Current.jpg

Basically, for each time slot I am given a number of figures for an area, a column of totals for that area to the right of each set of figures and a total at the bottom of that column which should reflect the total for the last set of figures from the latest set of figures for each zone. I may have overcomplicated things by coming up with the formula I did (shown here):

=SUM(SUM(N4:R4)+(SUM(SUM(N5:R5)-SUM(N4:R4)))+(SUM(SUM(N6:R6)-SUM(N5:R5)))+(SUM(SUM(N7:R7)-SUM(N6:R6)))+(SUM(SUM(N8:R8)-SUM(N7:R7)))+(SUM(SUM(N9:R9)-SUM(N8:R8))))+(SUM(SUM(N10:R10)-SUM(N9:R9)))+(SUM(SUM(N11:R11)-SUM(N10:R10)))+(SUM(SUM(N12:R12)-SUM(N11:R11)))+(SUM(SUM(N13:R13)-SUM(N12:R12)))+(SUM(SUM(N14:R14)-SUM(N13:R13)))+(SUM(SUM(N15:R15)-SUM(N14:R14)))+(SUM(SUM(N16:R16)-SUM(N15:R15)))+(SUM(SUM(N17:R17)-SUM(N16:R16)))

It was the best thing I could come up with to have a reflection of the latest total for each zone so I could then get an overall total for all zones in the bottom right corner.

If anyone can come up with a simpler solution, I would really appreciate it as I'm trying to get my head round this alongside a whole host of other responsibilities!

Many thanks

The hourly figures will either always increase or remain the same. The row at the bottom should always reflect the latest set of figures and the formulas only ever be applied to the most recent set of figures. That's the bit I can't get my head round. The formula is simple but it's telling it to only ever apply to the latest FULL set of figures. I've also added a couple of columns to the right as well as some total boxes down in the bottom right corner. These should also only apply to the latest full set of figures.

Current.jpg

Sure thing, although for now I've given up on the current formulas and have been selecting the boxes manually each time.

Current Sheet

Trying to make some sense of things, is this what you're looking for?

Please Login or Register  to view this content.

Yeah, for that bit that's correct as long as the figures that are fed into it are relative to the most recent set of figures ONLY, so the data cells put in will jump up a row each time.

Also, the 'Totals' row along the bottom should be identical to the most recent figures also.

Originally Posted by ilovewhat

that's correct as long as the figures that are fed into it are relative to the most recent set of figures ONLY

Can you post another sample file with some figures where the formula fails, type in the expected results there, and in the 'Totals' row.

Here's another image file explaining what I'm hoping to achieve and a current file for the same spreadsheet.

Current.jpg

Current

I can't download the last file for some reason, so this is based on the earlier one.

You absolute legend! That's exactly what I was after! I was hoping that it might make sense to me once I saw the answer but I'm still just as stumped by it but at least I have it all in place and working now! That's gonna make the rest of my weekend/summer SOOOOO much easier and in that time I'm hoping I can understand how it works in the first place

Thanks again, you've been a huge help!

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.