[Solved] Newbie needs help with spreadsheet formulas

[GS750]

I have tried everything today to get my spreadsheet working properly, using OO 3.3 and WIN7.
I know what I want it to do but I cant get it to work???

(Edited)
Hopefully a clearer explanation:

All are worked on the same row number.

"M5" has the price per item (say $1.23)
"D5" has the amount of items needed (say 2)
"N5" has the total of "M5" x "D5" but ONLY if there is a $$ value in "M5", if not then i want it to stay blank

So if "M5" has a value then it would multiply that by "D5" and "N5" would be: $1.23 x 2= $2.46


What do I put in "N5"?????


"O5" shows the total of GST (10%) of "N5" using this: =SUM(N5/10)

And "P5" gives me the total price : =SUM(N5+O5)

I managed to get it to stop it showing $0.00 values in columns "N", "O" and "P" , thanks to Villeroy

Application wide: menu:Tools>Options...Calc>View>Zero Values = OFF

I am a formula dummy but I have really been trying all day :'(

Ooh and if I can, I would also like column "Q" to compare and display the $$ difference between "P" and "K" cells

SPREADSHEET ERROR

[Villeroy]

Application wide: menu:Tools>Options...Calc>View>Zero Values = OFF

[GS750]

Application wide: menu:Tools>Options...Calc>View>Zero Values = OFF

Thanks for that That solves a part of my problem lol


Finally followed through and stumbled upon this as well:
=IF(M5>0;M5*D5)


Now all I need to get is:
Column "Q" to compare and display the $$ difference between "P", "K" and "G" cells but I will post that in a new thread if need be.

[MrProgrammer]

The easiest way is to use Zero Values = OFF, but that's a global setting which applies to all zero values in your spreadsheet. If there are some zero values that you do want to display, you can't use that setting, but there are other techniques which will work.

"N5" has the total of "M5" x "D5" but ONLY if there is a $$ value in "M5", if not then i want it to stay blank

The standard way would be =IF(ISBLANK(M5);"";M5*D5).

"O5" shows the total of GST (10%) of "N5" using this: =SUM(N5/10)

I'd use =IF(ISBLANK(M5);"";N5*0.1). Using the SUM function is redundant.

And "P5" gives me the total price : =SUM(N5+O5)

=IF(ISBLANK(M5);"";N5+O5), or
=IF(ISBLANK(M5);"";SUM(N5;O5)) (meaning sum of numbers in N5 and O5), but
=IF(ISBLANK(M5);"";SUM(N5+O5)) would be redundant (to use both the SUM function and the + operator), but will work.

Another technique is to use Format > Cells > Numbers and add ;"" to the end of the Format Code in the cells which should not display zeros. Here's where it would have been helpful to see your actual spreadsheet to examine the format code in the cells. I'm guessing it's something like [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00 so you would change that to [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;"". If you add ;"" you don't have to test for ISBLANK(cell). Format;Format;"" means "don't display anything if the value is zero".

All of these ideas will keep cells N5, O5, and P5 blank if M5 is blank, whether or not you use Zero Values = OFF.

Finally followed through and stumbled upon this as well: =IF(M5>0;M5*D5)

This only works if you have Zero Values = OFF, but in that case you can simply use =M5*D5.

display the $$ difference between "P", "K" and "G" cells

Difference suggests subtraction. Subtraction takes two operands. What sort of "difference" do you want for three operands in P, K, and G?

Edit: I see you have another topic to explain that.

For further postings, please do not simply attach a picture of the document— there are too many settings that can affect the content which cannot be determined from a picture. If is far more helpful to attach the actual document, or at least a small sample showing the problem. And you are more likely to get assistance because the volunteers don't have to take time to replicate your data/formulas in spreadsheets for testing of solutions.

If you're new to using Calc, it will help you to read Ten concepts that every Calc user should know.

[GS750]

For further postings, please do not simply attach a picture of the document— there are too many settings that can affect the content which cannot be determined from a picture. If is far more helpful to attach the actual document, or at least a small sample showing the problem. And you are more likely to get assistance because the volunteers don't have to take time to replicate your data/formulas in spreadsheets for testing of solutions.

If you're new to using Calc, it will help you to read Ten concepts that every Calc user should know.

Thanks for this reply Mr Programmer, I just saw it tonight and would like to thank you!

The info you provided was awesome and it really helped out with the main issues I had, now that those cells do not display $0.00 it has meant that my other results are now working

I am also hearing you about posting the actual sheet up, but I am new here and not sure how to do so??


I did read the ten concepts but I found it too difficult for my basic understanding to follow.

Thanks again for your further input and assistance, my sheet is now working very well

[MrProgrammer]

I am also hearing you about posting the actual sheet up, but I am new here and not sure how to do so??

See How to attach a document in the Survival Guide.

I did read the ten concepts but I found it too difficult for my basic understanding to follow.

I realize that the tutorial is quite terse. It is only intended to give you an overview. The online Help and the Calc Guide has a lot more detail. If you re-read Ten Concepts after working with Calc a bit, I'm sure more of it will make sense. And if you run into a new issue, look there and see if any of the concepts help with a solution. I think you'll struggle with spreadsheets until you have a basic understanding of those concepts, however this may take a while. Almost everyone takes some time to learn how to use spreadsheets effectively. It seems like a simple idea, but all modern spreadsheet programs are more sophisticated than most beginners realize.