Need help with conditional, calculating formulas

I'm attaching a workbook with which I need help from someone more familiar with somewhat advanced formulas. I can usually hold my own with simpler math functions, but these might be called conditional formulas, for lack of a better term. A textbox on the first of two pages hopefully explains what's needed. All sensitive, identifying information has been removed. If you prefer to just give me the formulas needed and the cell ref into which they need to go, I can copy/paste, then fill down. Many TIA for any help!

Edit: Mark_T updated uploaded file from @EarlEMelton with personal data removed.

07_Properties_2016_NSI.ods

Okay, Mark_T, I see the pencil now so will attach the file. Yes, I copy/pasted your formulas, filled down, then selected all the pertinent cells, went to Format Cells and pasted the format statement. I may not be doing it right as I still see the zeros. When I go back from there to the Format Cells thing again, that format statement has changed!!! Note that I also finished taking out all the sensitive data...I think! :-) Appreciate your help so much.

A thought: Should I have pasted the format statement one at a time in each cell, rather than selecting all the calculated cells and doing so only once? I'm afraid to try anything and mess it up further but can certainly take the time to do them individually if you say that would work better. Just let me know.

edit retag close merge delete

At least I've learned how to post a problem and attach a file much faster!

( 2016-11-16 17:18:29 +0200 )edit

@EarlEMelton, the sheets 9-16 and 10-16 and YTD Summary still seem to contain names and addresses, hopefullly they are only fakes but just thought I'd bring them to your attention.

( 2016-11-16 23:35:16 +0200 )edit

Shoot, I forgot all about those! Was primarily concerned with the main YTD Summary. Thanks for letting me know.

( 2016-11-17 02:59:35 +0200 )edit

Moved the info from your answer and comment to your question, and deleted that answer.

( 2016-11-18 04:47:53 +0200 )edit

If the answer solves your question please tick the ✔ on the right answer.

( 2016-11-19 15:14:46 +0200 )edit

Sort by » oldest newest most voted

I would suggest to use the cell number format to show blank for a zero value cell instead of using the IF formula.

From the example in cell O4 the formula would be:

=H4-I4-K4-M4+P4


And the cell number format changed to:

[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;""


The added ;"" will show the zero value cells as blank.

The #Value result in O29 seems to be due to the cell P29 is ='10-16'.O32, which contains =IF(H32-I32-K32-M32+P32=0,"", H32-I32-K32-M32+P32). I think a formula that returns "" is not treated as zero.

Edit to add: Set up the formula and the cell format for O4, then copy cell O4 and paste that to the range of cells O4:O32

Set the same cell format for P4, then copy and paste special, formats only to P5:P32.

Similar change to sheet 10-16 should fix the #Value error.

more

Thanks loads! I'll be looking into this more tomorrow or Friday. Can soomebody delete the upload since I goofed and left data in it that I shouldn't have?

( 2016-11-17 03:01:33 +0200 )edit

Removed the upload from the question as requested, although it does mean that now its not easy for anyone to understand what you were asking. If you can then edit your question to upload a modified file, or post it in an answer and I'll edit into your question.

( 2016-11-17 06:01:44 +0200 )edit

Mark_T, Ok, I have the modified file ready to upload but there is no Edit button above on the original post, only Flag, Close, and Delete. Am I going to have to close this one and start a new one again? I can sure do that, but it seems like there ought to be an easier way.

( 2016-11-17 15:54:44 +0200 )edit

@EarlEMelton, probably you don't have enough Karma points to edit your own questions, I know it seems stupid right? Post the attachment in an answer to your own question, then I'll copy it to your question and delete your fake answer. Did you try the suggestion to use cell format to blank zeros?

( 2016-11-17 19:29:25 +0200 )edit

What fucking logic says you're allowed to ask a question and must agree to some rules, but you're not allowed to explain yourself better in edits to your -own- question ? That's purely retarded.

( 2016-11-17 20:54:33 +0200 )edit

I don't even see it defined anywhere how much karma is needed to edit your own post. 75 to edit wiki questions, maybe it's the same to edit your own posts. I used to think it was just that new members didn't know where to find the edit button, but certainly doesn't seem to be true in this case as EarlEMelton sees the flag, close and delete.

( 2016-11-17 22:41:30 +0200 )edit

Mark, All appears to be solved at this time. I was royally screwing things up by only applying your suggestions to the current November sheet. Of course the November sheet drew much of its info from October and so on. Long story short? I applied the format code and your formula to the September, October AND November sheets and everything is just like I want it. So what am I trying to say here? YOU DA MAN!

You are welcome to remove all uploaded WBs and I'll close the question. If I can find how to do so. :-)

more

Could you also check the tick mark to the left of the answer if that was the correct answer, Thanks.

( 2016-11-19 04:12:38 +0200 )edit

Stats

Asked: 2016-11-16 17:08:48 +0200

Seen: 88 times

Last updated: Nov 19 '16