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

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


Eamon <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...
>
> =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

"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:[email protected]...
> is there an underscore before <g46> in the formula for g48?

No. it is a minus sign -.


> Eamon <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

Try:

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


Mangesh




"Eamon" <[email protected]> wrote in message
news:[email protected]...
>
> "mangesh_yadav"
<[email protected]>
> wrote in message
> news:[email protected]...
> >
> > =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,

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" <[email protected]> wrote in message
news:[email protected]...
> Try:
>
> =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
> and
> =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")










>
>
> Mangesh
>
>
>
>
> "Eamon" <[email protected]> wrote in message
> news:[email protected]...
>>
>> "mangesh_yadav"
> <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > =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
>>
>>
>
>

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

Best regards,

Eamon


"Eamon" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Try:
>>
>> =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
>> and
>> =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")
>
>
>
>
>
>
>
>
>
>
>>
>>
>> Mangesh
>>
>>
>>
>>
>> "Eamon" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>> "mangesh_yadav"
>> <[email protected]>
>>> wrote in message
>>> news:[email protected]...
>>> >
>>> > =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
>>>
>>>
>>
>>
>
>

Hi Eamon,

Good you solved it. Thanks for the feedback.

Mangesh



"Eamon" <[email protected]> wrote in message
news:[email protected]...
> Have it solved, thanks to everyone who replied, your help was much
> appreciated, and a special thank you to Mangesh.
>
> Best regards,
>
> Eamon
>
>
> "Eamon" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> Try:
> >>
> >> =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
> >> and
> >> =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >>
> >>
> >> Mangesh
> >>
> >>
> >>
> >>
> >> "Eamon" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>
> >>> "mangesh_yadav"
> >> <[email protected]>
> >>> wrote in message
> >>> news:[email protected]...
> >>> >
> >>> > =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
> >>>
> >>>
> >>
> >>
> >
> >
>
>