really need help with IF syntax.... :-\

i have an event spreadsheet that i've created to calculate a fee, the payment received for the fee and the difference due (or overpaid). if there are no amounts entered to make up one of the formulas, i want the cell to show blank. right now it puts $0.00 in it.

i've attached a sample of the spreadsheet. here are the two formulas i have in it.

*formula in column O: =SUM(G4:N4) add all amounts between G and N and put the total here. if there are no numbers in G thru N, don't put anything here.

*formula in column S: =P4-O4 subtract the payment (P4) from what is due (O4) and put the amount here. if there are no numbers in O, don't put anything here.

in the attached spreadsheet, line 3:, henry miller sent in his form/payment for what days and meals he's coming, so the formulas in columns O and S have numbers in them, because the related cells have numbers in them.

however, john doe said he's coming to the event verballd, but he didn't send form/payment in, so i can't enter any numbers in the sheet yet; but i want to record his name and that he's coming. and instead of columns O and S having 0.00 in them, i want the formula to be there, but the cell blank (until numbers are entered into the other cells).

so....can anyone tell me exactly what to put for the formulas in columns O and S to do what i want it to do? i have been trying to read tutorials on the IF function and i can't make heads or tails of it.

i hope that makes sense. let me know if you have a question.

thanks! :-)

Use this in O4:

=IF(SUM(G4:N4)=0,"",SUM(G4:N4))

and this in S4:

=IF(O4="","",P4-O4)

then copy the formulae down.

Hope this helps.

Pete

thank you, thank you! i'm at home right now (no computer), but i'll give these tips a try tomorrow, and let you know what happens! ☺

bingo! we have a winner!!

while i'm sure both solutions provided would have worked, the easiest to try was changing the excel settings, which protonLeah suggested. voila! it blanked out all of the cells with a zero value in them.

thanks again!! i just love forums like this where you can ask a question and smarter brains than mine know the answers...ha!

hmmm....i'll have to think about that. i'll think i'll try it both ways, and see which one makes the most sense. if someone's all paid up, it probably should show zero...

thanks for thinking of that! :-)

okay....so i put the formulas that pete_uk provided in (as fdibbins suggested) and yes, that is probably better cuz it shows blank if nothing has been paid at all, but it shows zero if the amount due minus the amount paid equals zero.

thanks again! :-)

Also, my solution helps you with the IF syntax, which is what you had asked for.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Originally Posted by Pete_UK

Also, my solution helps you with the IF syntax, which is what you had asked for.

yes, it does...thanks! :-)

Originally Posted by Pete_UK

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

done! :-)

Originally Posted by Pete_UK

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

good to know! done, done and done!

thanks all! :-)

Originally Posted by Pete_UK

Also, my solution helps you with the IF syntax, which is what you had asked for.

sometimes i don't know for sure *what* i'm asking for....ha! but in any case, two awesome solutions were provided. :-)

And thank you for feeding back, and for the Reps.

Pete