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(F160,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


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=508628

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

--
Regards,

Peo Sjoblom

Portland, Oregon




"streetboarder"
wrote in message
news:streetboarder.22qroy_1139118301.0945@excelfor um-nospam.com...

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(F160,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


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile:
http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=508628

Try something like:

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


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=508628

Awesome - Thanks guys.

Josh


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=508628

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
--
paul
remove nospam for email addy!



"streetboarder" wrote:


Awesome - Thanks guys.

Josh


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=508628

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),F160),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


--
streetboarder
------------------------------------------------------------------------
streetboarder's Profile: http://www.excelforum.com/member.php...o&userid=30707
View this thread: http://www.excelforum.com/showthread...hreadid=508628

Perhaps this would be better for you

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=508628

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

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

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

For conditional formatting I would do something like this, select all
the QTY cells again and go to Format/Condtional Formatting, in the drop
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 the
background, that way whenever a price appears in D16 F16 will be
highlighted drawing the attention of the user to that cell.

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

HTH
Jean-Guy


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=508628