MrExcel Publishing
Your One Stop for Excel Tips & Solutions

FORMULA


Posted by Joanne Stanley on November 22, 2001 6:26 AM

I need to calculate a formula so that it gives me a total and not #VALUE

It needs to calculate information in three cells

What I need to do is to make it so that if cell1 contains a value of one then the total must be blank (FALSE) but in the same formula i also need it to give me a value if cell2 is more than 1. Cell three is the value and does not need to be calculated just moved to the new cell and only show a value if it covers the criteria of the above.


Posted by Paul Akkermans on November 22, 2001 6:49 AM


Something like this?
If A1 = 1 or B1 > 1 it will be blank else the value of D1 is shown.

=IF(OR(A1=1;B1>1);"";D1)

Posted by joanne Stanley on November 22, 2001 7:08 AM

still a bit confused have tried formula but isnt working will try and explain more clearly

Cell 1 contains value 1 or is blank
Cell 2 contains invoice numbers beginning with 1 or are blank
Cell 3 is the invoice total
I need to have a new column which will only show invoice total if
cell 1 is blank
cell 2 has an invoice number beginning with 1

if cell 2 has an invoice number in it then with the formula i am using it shows a VALUE i need to have it so even if cell2 has an invoice number and cell1 is blank then the value is blank

Here is the formula I am using which is not working
=IF(L6>1,SUM(I6)," ")*AND(IF(D6<1,SUM(I6)," "))


Posted by Paul Akkermans on November 22, 2001 7:24 AM


Try this one

=IF(AND(L6>1,D6<1),SUM(I6)," ")