ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Please help with formula (https://www.excelbanter.com/excel-worksheet-functions/29103-please-help-formula.html)

Eamon
Please help with formula
 
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

R.VENKATARAMAN
is there an underscore before <g46 in the formula for g48?


Eamon wrote in message
...
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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376241

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376241

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



"dominicb" wrote in
message ...

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile:
http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376241

Eamon

"mangesh_yadav"
wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=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

Snip

Eamon

"R.VENKATARAMAN" wrote in message
...
is there an underscore before <g46 in the formula for g48?

No. it is a minus sign -.


Eamon wrote in message
...
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
Try:

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


Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=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

Snip

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 A44:D48 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


"Mangesh Yadav" wrote in message
...
Try:

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












Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=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

Snip

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

Best regards,

Eamon


"Eamon" wrote in message
...
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 A44:D48 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


"Mangesh Yadav" wrote in message
...
Try:

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












Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=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

Snip

Mangesh Yadav
Hi Eamon,

Good you solved it. Thanks for the feedback.

Mangesh



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

Best regards,

Eamon


"Eamon" wrote in message
...
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 A44:D48 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


"Mangesh Yadav" wrote in message
...
Try:

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












Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=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

Snip


All times are GMT +1. The time now is 02:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com