#Value! problem

[nickharr]

I wonder if someone could help me with sorting out a formular that will allow a cell to a zero rather than #Value!.
To make things simple - I have 5 columns - A has the Product Name, B has the Number Ordered, C has the price of the product, D has the Number delivered and E is the total of the products ordered (B1*D1).
I need to put in column D the words "Out of Stock" if a product cannot be delivered. If I do this I get #Value! in the cell E1 whereas I would like it to be taken as a zero and therefore E1 would also show a zero.
I realise the simplest way would be to put in a zero instead of "Out of Stock" but this would mess up other parts of the sheet if I did!
If anyone could help I would be most grateful.

 

[Hermanito]

the B1*D1 formula, shouldn't that be B1*C1??
number of products ordered times number of products delivered seems meaningless to me, no?

How do you determine if a product cannot be delivered?

 

[Aladin Akyurek]

Change

=B1*D1

to

=B1*N(D1)

 

[nickharr]

Sorry for my mistake - you are quite right - it actually should have been the price of the product (C) multiplied by the number delivered (D).
However, after your suggestion (Aladin) of puting in C1*n(D1) - it looks like working a treat - thanks very much!

 

[Aladin Akyurek]

Sorry for my mistake - you are quite right - it actually should have been the price of the product (C) multiplied by the number delivered (D).
However, after your suggestion (Aladin) of puting in C1*n(D1) - it looks like working a treat - thanks very much!

You are welcome. Thanks for providing us feedback.

 

[nickharr]

Hello again - I thought I had put this further query on this thread but can't see it so I probably pressed the wrong button!
Just as a follow up - on this same sheet I do have a fair number of other columns and in one of them I need to divide a coloumn with the total costs plus carriage etc. For simplicity I will call it column F - so I need to divide F1/D1 and at the moment it comes up with #Value! and I need it to come up with a zero. I tried F1/n(D1) but then it comes up with #Div/0!.
Any thoughts?!

 

[Aladin Akyurek]

Hello again - I thought I had put this further query on this thread but can't see it so I probably pressed the wrong button!
Just as a follow up - on this same sheet I do have a fair number of other columns and in one of them I need to divide a coloumn with the total costs plus carriage etc. For simplicity I will call it column F - so I need to divide F1/D1 and at the moment it comes up with #Value! and I need it to come up with a zero. I tried F1/n(D1) but then it comes up with #Div/0!.
Any thoughts?!

=IF(N(D1),F1/N(D1),0)

If F1 also might house text values...

=IF(N(D1),N(F1)/N(D1),0)

 

[nickharr]

Once again Aladin - you have come up trumps! Really appreciate your help.

 

[Aladin Akyurek]

Once again Aladin - you have come up trumps! Really appreciate your help.

You are welcome. I appreciate the feedback.