Please help with formula

[Eamon]

Hello,





I have a spreadsheet that is set up as an invoice.

When I run a macro to create a new invoice it clears the details that were
entered in the previous invoice and when the new invoice opens before I
enter any details I am getting the following error #VALUE! in G:48
(Discount) and G:49 (Total).

The formula I have in G:48 is =-G46*DISCOUNT (Discount is a named range in
H:9)

The formula I have in G:49 is =SUM(G46:G48)

Could somebody please help me with rewriting the formulas in G:48 and G:49
so as not to get the error #VALUE! when a new invoice is opened. Ideally I
would like these cells to be blank when the new invoice is created.



Any help or suggestions would be most welcome.





Eamon

 

[mangesh_yadav]

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))

Mangesh

 

[dominicb]

Good morning Eamon

I was just going to answer this, but notice you've just got a reply.

An alternative would be to got to Tools > Options, View, and then
uncheck zero values. This would suppress zeros across your whole
workbook.

HTH

DominicB

 

[Eamon]

Dominic,

I had zero values unchecked, but I am still getting the #VALUE! error when a
new invoice is opened.
Anything else I could try?

Eamon

 

[Eamon]

"mangesh_yadav" <[email protected]>
wrote in message

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))

Mangesh

Thanks for your help.

I have entered your formulas as shown, but i am still getting the #Value!
error when a new invoice is created

Eamon

 

[Eamon]

is there an underscore before <g46> in the formula for g48?

No. it is a minus sign -.

 

[Mangesh Yadav]

Try:

=IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
and
=IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")


Mangesh

 

[Eamon]

Mangesh,

Your formula gets rid of the #VALUE! Error. Thank you.
See below for detail of how the spreadsheet is laid out.

Potential problem now.
1) If someone just purchases a part from the garage, I am getting #VALUE for
TAX G47
Or
2) If someone for example had a minor repair carried out that did not
require any parts, just labour I am getting #VALUE for TAX G47

When parts and labour are used it works fine.

Any suggestions to correct this please.



In cells B17:G43 I have parts that the garage may sell or are used in
repairs.
Columns:
B Code
C Product Description
D Format
E Price
F Quantity
G Total


In cells A4448 I have details of Labor
Columns:
A Service Person
B Hours
C Rate
D Amount

D49 (TOTAL) =IF(SUM(E45:E48),SUM(E45:E48),"")


G44 (PARTS) =IF(SUM(G17:G43),SUM(G17:G43),"")
G45 (LABOR) =IF(SUM(E49),E49,"")
G46 (SUB TOTAL) =IF(SUM(G43:G45),SUM(G43:G45),"")
G47 (TAX) =IF(OR(SUM(G44)>0,G45),(PARTS_TAX*G44)+(LABOUR_TAX*G45),"")

(PARTS_TAX is in D14) and (LABOUR_TAX is in G14)

G48 (DISCOUNT) =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")

(DISCOUNT is in H14)

G49 (TOTAL) =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")

Eamon

 

[Eamon]

Have it solved, thanks to everyone who replied, your help was much
appreciated, and a special thank you to Mangesh.

Best regards,

Eamon

 

[Mangesh Yadav]

Hi Eamon,

Good you solved it. Thanks for the feedback.

Mangesh