If Statement - Need Help

[streetboarder]

Trying to make a fool proof pricing sheet - if there is such a thing

Price = D16
Suggest = E16
Quantity = F16
Total = G16

Here is my current If Statement:
=IF(F16>0,D16*F16,"")

This seems to be working fine until today someone typed in "text" into
the QTY Field. How can I change the if statement to not include the
text?

Thanks!
Josh

 

[Peo Sjoblom]

=IF(AND(ISNUMBER(F16),F16>0),D16*F16,"")

--
Regards,

Peo Sjoblom

Portland, Oregon




"streetboarder" <[email protected]>
wrote in message
news:[email protected]...

 

[pinmaster]

Try something like:

=IF(ISNUMBER(F16),D16*F16,"")


HTH
JG

 

[streetboarder]

Awesome - Thanks guys.

Josh

 

[Guest]

making a fool proof sheet can be very testing.....i would data validate that
column for
either whole or decimal numbers say 1 to 10000

 

[streetboarder]

Validation is a good idea however I would like make this sheet as user
friendly as possible (since after all this is how we get our orders).

'=IF(AND(ISNUMBER(F16),F16>0),D16*F16,"please enter #")

I am thinking more along the lines of the above but for some reason I
can't get it to work correctly. If the cell is blank or their is a zero
in the QTY colume it returns "please enter #".

Any help would be appreciated.

Thanks,
Josh

 

[daddylonglegs]

Perhaps this would be better for you

=IF(ISTEXT(F16),"please enter #",D16*F16)

 

[flummi]

Don't quite get the point.
Isn't that what you are saying in your formula?

If F16 is NOT an number OR if it is ZERO, display "please enter #".

Hans

 

[pinmaster]

If you really want to make it fool proof then I would use dat
validation and conditional formatting.

For data validation first select all the QTY cells in column F, then g
to data validation, in the "Allow" menu select "Custom" and in th
formula box type: =ISNUMBER(F16) where F16 is the first cell in th
selection, next click on the "Error Alert" tab and leave a message fo
the user explaining that no text is allowed, this message will appea
whenever someone tries to enter text in the cell.

For conditional formatting I would do something like this, select al
the QTY cells again and go to Format/Condtional Formatting, in the dro
down menu you have "Formula is" and "Cell value is", select "Fomula is
and type =ISNUMBER(D16) click on format and choose a color for th
background, that way whenever a price appears in D16 F16 will b
highlighted drawing the attention of the user to that cell.

That in conjunction with your formula should make it easier for th
user.

HTH
Jean-Gu