Calculations based on 4 possible text strings?

I have this formula that goes into E2 in the first line:
=IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
which works if the text string PST+GST is found in D2.  But I don't know how 
to branch out from there.  There are 4 possible answers to go into D2 which 
will affect outcome of E2.  They are, and I'll list them all including the 
PST+GST case above:

if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both taxes 
are equal where for years they were 0.06 and 0.07]
if D2 = text string "Neither", E2 = nothing, no change to C2

I'm using the above formula because that's what I know more or less how to 
do.  But there might be something better (?).

Also, when playing around with figures, I have had some FALSE and #VALUE 
appear, so hoping to cross that bridge when I get to it. <g>

Thanks!  :oD


0
NoSpam5247 (64)
10/3/2007 5:43:37 PM
excel 39879 articles. 2 followers. Follow

11 Replies
673 Views

Similar Articles

[PageSpeed] 34

Assuming that the taxes will at some time be different again try:

=IF(C2="","",IF(D2="PST+GST",C2*0.13,IF(D2="PST",C2*0.6,IF(D2="GST",C2*0.06,""))))

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>I have this formula that goes into E2 in the first line:
> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
> which works if the text string PST+GST is found in D2.  But I don't know 
> how to branch out from there.  There are 4 possible answers to go into D2 
> which will affect outcome of E2.  They are, and I'll list them all 
> including the PST+GST case above:
>
> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both 
> taxes are equal where for years they were 0.06 and 0.07]
> if D2 = text string "Neither", E2 = nothing, no change to C2
>
> I'm using the above formula because that's what I know more or less how to 
> do.  But there might be something better (?).
>
> Also, when playing around with figures, I have had some FALSE and #VALUE 
> appear, so hoping to cross that bridge when I get to it. <g>
>
> Thanks!  :oD
>
>
> 


0
sandymann2 (1054)
10/3/2007 6:04:54 PM
=IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>I have this formula that goes into E2 in the first line:
> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
> which works if the text string PST+GST is found in D2.  But I don't know 
> how to branch out from there.  There are 4 possible answers to go into D2 
> which will affect outcome of E2.  They are, and I'll list them all 
> including the PST+GST case above:
>
> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both 
> taxes are equal where for years they were 0.06 and 0.07]
> if D2 = text string "Neither", E2 = nothing, no change to C2
>
> I'm using the above formula because that's what I know more or less how to 
> do.  But there might be something better (?).
>
> Also, when playing around with figures, I have had some FALSE and #VALUE 
> appear, so hoping to cross that bridge when I get to it. <g>
>
> Thanks!  :oD
>
> 


0
bob.NGs1 (1661)
10/3/2007 6:06:22 PM
Ooops forgot Neither but Bob's formula is better.

-- 

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:eO2fkfeBIHA.748@TK2MSFTNGP04.phx.gbl...
> Assuming that the taxes will at some time be different again try:
>
> =IF(C2="","",IF(D2="PST+GST",C2*0.13,IF(D2="PST",C2*0.6,IF(D2="GST",C2*0.06,""))))
>
> -- 
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
> news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>>I have this formula that goes into E2 in the first line:
>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
>> which works if the text string PST+GST is found in D2.  But I don't know 
>> how to branch out from there.  There are 4 possible answers to go into D2 
>> which will affect outcome of E2.  They are, and I'll list them all 
>> including the PST+GST case above:
>>
>> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
>> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
>> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both 
>> taxes are equal where for years they were 0.06 and 0.07]
>> if D2 = text string "Neither", E2 = nothing, no change to C2
>>
>> I'm using the above formula because that's what I know more or less how 
>> to do.  But there might be something better (?).
>>
>> Also, when playing around with figures, I have had some FALSE and #VALUE 
>> appear, so hoping to cross that bridge when I get to it. <g>
>>
>> Thanks!  :oD
>>
>>
>>
>
>
> 


0
sandymann2 (1054)
10/3/2007 6:11:30 PM
Thanks, Sandy!!  :oD

"Sandy Mann" <sandymann2@mailinator.com> wrote in message 
news:%23QdMQjeBIHA.324@TK2MSFTNGP04.phx.gbl...
> Ooops forgot Neither but Bob's formula is better.
>
> -- 
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Sandy Mann" <sandymann2@mailinator.com> wrote in message 
> news:eO2fkfeBIHA.748@TK2MSFTNGP04.phx.gbl...
>> Assuming that the taxes will at some time be different again try:
>>
>> =IF(C2="","",IF(D2="PST+GST",C2*0.13,IF(D2="PST",C2*0.6,IF(D2="GST",C2*0.06,""))))
>>
>> -- 
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
>> news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>>>I have this formula that goes into E2 in the first line:
>>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
>>> which works if the text string PST+GST is found in D2.  But I don't know 
>>> how to branch out from there.  There are 4 possible answers to go into 
>>> D2 which will affect outcome of E2.  They are, and I'll list them all 
>>> including the PST+GST case above:
>>>
>>> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
>>> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
>>> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both 
>>> taxes are equal where for years they were 0.06 and 0.07]
>>> if D2 = text string "Neither", E2 = nothing, no change to C2
>>>
>>> I'm using the above formula because that's what I know more or less how 
>>> to do.  But there might be something better (?).
>>>
>>> Also, when playing around with figures, I have had some FALSE and #VALUE 
>>> appear, so hoping to cross that bridge when I get to it. <g>
>>>
>>> Thanks!  :oD
>>>
>>>
>>>
>>
>>
>>
>
> 


0
NoSpam5247 (64)
10/3/2007 6:52:41 PM
"Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
news:%23O%23GWgeBIHA.4956@TK2MSFTNGP06.phx.gbl...
> =IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))

Great!  A whole new way of doing things that I'd never seen before.  Another 
formula to go into my TIPS folder <g>.

I actually only ran into a couple of challenges, any way to not have the 2nd 
result for "Neither" as $0.00, not show up?  I'd like to be able to choose 
that "Neither" option, but I think I'd prefer that the cell remain blank and 
not show up as $0.00.

Also, when no text string is chosen and the cell is just blank, a #N/A 
appears in E2.  Can you recommend how to handle this one, by any chance?

Thanks much!  This is great.  :oD

> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
> news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>>I have this formula that goes into E2 in the first line:
>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
>> which works if the text string PST+GST is found in D2.  But I don't know 
>> how to branch out from there.  There are 4 possible answers to go into D2 
>> which will affect outcome of E2.  They are, and I'll list them all 
>> including the PST+GST case above:
>>
>> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
>> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
>> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both 
>> taxes are equal where for years they were 0.06 and 0.07]
>> if D2 = text string "Neither", E2 = nothing, no change to C2
>>
>> I'm using the above formula because that's what I know more or less how 
>> to do.  But there might be something better (?).
>>
>> Also, when playing around with figures, I have had some FALSE and #VALUE 
>> appear, so hoping to cross that bridge when I get to it. <g>
>>
>> Thanks!  :oD
>>
>>
>
> 


0
NoSpam5247 (64)
10/3/2007 7:01:40 PM
=IF(OR(D2={"Neither",""}),"",IF(C2="","",C2*LOOKUP(D2,{"GST","PST","PST+GST"},{0.06,0.06,0.13})))

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
news:eYgSW$eBIHA.1208@TK2MSFTNGP03.phx.gbl...
> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
> news:%23O%23GWgeBIHA.4956@TK2MSFTNGP06.phx.gbl...
>> =IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))
>
> Great!  A whole new way of doing things that I'd never seen before. 
> Another formula to go into my TIPS folder <g>.
>
> I actually only ran into a couple of challenges, any way to not have the 
> 2nd result for "Neither" as $0.00, not show up?  I'd like to be able to 
> choose that "Neither" option, but I think I'd prefer that the cell remain 
> blank and not show up as $0.00.
>
> Also, when no text string is chosen and the cell is just blank, a #N/A 
> appears in E2.  Can you recommend how to handle this one, by any chance?
>
> Thanks much!  This is great.  :oD
>
>> -- 
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my 
>> addy)
>>
>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message 
>> news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>>>I have this formula that goes into E2 in the first line:
>>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
>>> which works if the text string PST+GST is found in D2.  But I don't know 
>>> how to branch out from there.  There are 4 possible answers to go into 
>>> D2 which will affect outcome of E2.  They are, and I'll list them all 
>>> including the PST+GST case above:
>>>
>>> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
>>> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
>>> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both 
>>> taxes are equal where for years they were 0.06 and 0.07]
>>> if D2 = text string "Neither", E2 = nothing, no change to C2
>>>
>>> I'm using the above formula because that's what I know more or less how 
>>> to do.  But there might be something better (?).
>>>
>>> Also, when playing around with figures, I have had some FALSE and #VALUE 
>>> appear, so hoping to cross that bridge when I get to it. <g>
>>>
>>> Thanks!  :oD
>>>
>>>
>>
>>
>
> 


0
bob.NGs1 (1661)
10/3/2007 11:32:22 PM
> Great!  A whole new way of doing things that I'd never seen before.

What about this... assuming your possible choices for D2 (Neither, PST, GST 
and PST+GST) are controlled by Data Validation so that no other entries are 
possible, you could use this formula...

=0.06*C2*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST"))

Of course, this returns 0 when Neither is entered and you stated that...

> I'd like to be able to choose that "Neither" option, but I think
> I'd prefer that the cell remain blank and not show up as $0.00.
>
> Also, when no text string is chosen and the cell is just blank

So, to account for this, you could modify the above formula using the test 
that Bob posted...

=IF(OR(D2={"Neither",""}),"",0.06*C2*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST")))

Rick 

0
10/4/2007 6:18:20 AM
That returns 0.12 for PST+GST, whereas OP wanted 0.13


-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in 
message news:O9U0Y5kBIHA.2268@TK2MSFTNGP02.phx.gbl...
>> Great!  A whole new way of doing things that I'd never seen before.
>
> What about this... assuming your possible choices for D2 (Neither, PST, 
> GST and PST+GST) are controlled by Data Validation so that no other 
> entries are possible, you could use this formula...
>
> =0.06*C2*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST"))
>
> Of course, this returns 0 when Neither is entered and you stated that...
>
>> I'd like to be able to choose that "Neither" option, but I think
>> I'd prefer that the cell remain blank and not show up as $0.00.
>>
>> Also, when no text string is chosen and the cell is just blank
>
> So, to account for this, you could modify the above formula using the test 
> that Bob posted...
>
> =IF(OR(D2={"Neither",""}),"",0.06*C2*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST")))
>
> Rick 


0
bob.NGs1 (1661)
10/4/2007 8:04:50 AM
Hmm! It seems I misread the original problem. Thanks for noticing that.

Okay, we can still save the COUNTIF approach. For the 1st formula I posted, 
use this instead...

=C2*ROUNDDOWN(0.065*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST")),2)

And for the second formula, use this instead...

=IF(OR(D2={"Neither",""}),"",C2*ROUNDDOWN(0.065*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST")),2))

Rick


"Bob Phillips" <bob.ngs@somewhere.com> wrote in message 
news:eKFJ10lBIHA.4476@TK2MSFTNGP06.phx.gbl...
> That returns 0.12 for PST+GST, whereas OP wanted 0.13
>
>
> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my 
> addy)
>
> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in 
> message news:O9U0Y5kBIHA.2268@TK2MSFTNGP02.phx.gbl...
>>> Great!  A whole new way of doing things that I'd never seen before.
>>
>> What about this... assuming your possible choices for D2 (Neither, PST, 
>> GST and PST+GST) are controlled by Data Validation so that no other 
>> entries are possible, you could use this formula...
>>
>> =0.06*C2*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST"))
>>
>> Of course, this returns 0 when Neither is entered and you stated that...
>>
>>> I'd like to be able to choose that "Neither" option, but I think
>>> I'd prefer that the cell remain blank and not show up as $0.00.
>>>
>>> Also, when no text string is chosen and the cell is just blank
>>
>> So, to account for this, you could modify the above formula using the 
>> test that Bob posted...
>>
>> =IF(OR(D2={"Neither",""}),"",0.06*C2*(COUNTIF(D2,"=PST*")+COUNTIF(D2,"=*GST")))
>>
>> Rick
>
> 

0
10/4/2007 8:48:45 AM
"Bob Phillips" <bob.ngs@somewhere.com> wrote in message
news:uGnpeWhBIHA.1208@TK2MSFTNGP05.phx.gbl...
> =IF(OR(D2={"Neither",""}),"",IF(C2="","",C2*LOOKUP(D2,{"GST","PST","PST+GST"},{0.06,0.06,0.13})))

Hi!  Thanks for this.  I had to adjust for the cells.  A lot gets lost in
translation from XL to words, eh? <g>  I tried this:

=IF(OR(E2={"Neither",""}),"",IF(D2="","",D2*LOOKUP(E2,{"GST","PST","PST+GST"},{0.06,0.06,0.13})))

But now I get #VALUE! in the 2 adjacent cells, so there's something I'm
doing wrong and I don't know what.

So I'm going back to my original formula and trying to see how to fix that.
It works in every way except for the 2 little nitpicky things (though these
types of things do hang up users, which is why I try to fix them).

Okay, back to square one, when I put a value into D2, 19.99, before I choose
whether or not tax has to be calculated in E2, I get #N/A in F2 (the tax
alone calculation) and another #N/A in G2 (which sums both D2 and the tax in
F2.

Naturally, once I choose which tax in E2, then all the rest of the line is
fine.  #N/A disappears and dollar amounts appear in all the other cells
including the running total cell in H2.

That's the first problem.

The second is when "Neither" is used for straight expense where no tax is
involved.  I get a $0.00 in the tax cell of E2.  The only way two ways I
know how to remove zeroes is 1)  to have somewhere a "" in the formula, if
memory serves.  But I've tried putting that into the working formula below
and I get the old #VALUE! again, so definitely I'm doing something wrong.

=IF(D2="","",D2*LOOKUP(E2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))

The second way is through conditional formatting.  Unfortunately, I'm using
alternate row colouring on this spreadsheet so that's out ... also, I think
sometimes the calculations are off, no?, because the zero isn't seen ?? (or
am I confusing this with something else.

<sigh>  Sorry this one has turned out to be tough.  I hope I've explained
better so problem is clearer.  Let me know.

> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
> news:eYgSW$eBIHA.1208@TK2MSFTNGP03.phx.gbl...
>> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
>> news:%23O%23GWgeBIHA.4956@TK2MSFTNGP06.phx.gbl...
>>> =IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))
>>
>> Great!  A whole new way of doing things that I'd never seen before.
>> Another formula to go into my TIPS folder <g>.
>>
>> I actually only ran into a couple of challenges, any way to not have the
>> 2nd result for "Neither" as $0.00, not show up?  I'd like to be able to
>> choose that "Neither" option, but I think I'd prefer that the cell remain
>> blank and not show up as $0.00.
>>
>> Also, when no text string is chosen and the cell is just blank, a #N/A
>> appears in E2.  Can you recommend how to handle this one, by any chance?
>>
>> Thanks much!  This is great.  :oD
>>
>>> -- 
>>> HTH
>>>
>>> Bob
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
>>> news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>>>>I have this formula that goes into E2 in the first line:
>>>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
>>>> which works if the text string PST+GST is found in D2.  But I don't
>>>> know how to branch out from there.  There are 4 possible answers to go
>>>> into D2 which will affect outcome of E2.  They are, and I'll list them
>>>> all including the PST+GST case above:
>>>>
>>>> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
>>>> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
>>>> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both
>>>> taxes are equal where for years they were 0.06 and 0.07]
>>>> if D2 = text string "Neither", E2 = nothing, no change to C2
>>>>
>>>> I'm using the above formula because that's what I know more or less how
>>>> to do.  But there might be something better (?).
>>>>
>>>> Also, when playing around with figures, I have had some FALSE and
>>>> #VALUE appear, so hoping to cross that bridge when I get to it. <g>
>>>>
>>>> Thanks!  :oD
>>>>
>>>>
>>>
>>>
>>
>>
>
>



0
NoSpam5247 (64)
10/5/2007 1:15:18 PM
"Bob Phillips" <bob.ngs@somewhere.com> wrote in message
news:uGnpeWhBIHA.1208@TK2MSFTNGP05.phx.gbl...
> =IF(OR(D2={"Neither",""}),"",IF(C2="","",C2*LOOKUP(D2,{"GST","PST","PST+GST"},{0.06,0.06,0.13})))

Hi!  Thanks for this.  I had to adjust for the cells.  A lot gets lost in
translation from XL to words, eh? <g>  I tried this:

=IF(OR(E2={"Neither",""}),"",IF(D2="","",D2*LOOKUP(E2,{"GST","PST","PST+GST"},{0.06,0.06,0.13})))

But now I get #VALUE! in the 2 adjacent cells, so there's something I'm
doing wrong and I don't know what.

So I'm going back to my original formula and trying to see how to fix that.
It works in every way except for the 2 little nitpicky things (though these
types of things do hang up users, which is why I try to fix them).

Okay, back to square one, when I put a value into D2, 19.99, before I choose
whether or not tax has to be calculated in E2, I get #N/A in F2 (the tax
alone calculation) and another #N/A in G2 (which sums both D2 and the tax in
F2.

Naturally, once I choose which tax in E2, then all the rest of the line is
fine.  #N/A disappears and dollar amounts appear in all the other cells
including the running total cell in H2.

That's the first problem.

The second is when "Neither" is used for straight expense where no tax is
involved.  I get a $0.00 in the tax cell of E2.  The only way two ways I
know how to remove zeroes is 1)  to have somewhere a "" in the formula, if
memory serves.  But I've tried putting that into the working formula below
and I get the old #VALUE! again, so definitely I'm doing something wrong.

=IF(D2="","",D2*LOOKUP(E2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))

The second way is through conditional formatting.  Unfortunately, I'm using
alternate row colouring on this spreadsheet so that's out ... also, I think
sometimes the calculations are off, no?, because the zero isn't seen ?? (or
am I confusing this with something else.

<sigh>  Sorry this one has turned out to be tough.  I hope I've explained
better so problem is clearer.  Let me know.

> -- 
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
> news:eYgSW$eBIHA.1208@TK2MSFTNGP03.phx.gbl...
>> "Bob Phillips" <bob.ngs@somewhere.com> wrote in message
>> news:%23O%23GWgeBIHA.4956@TK2MSFTNGP06.phx.gbl...
>>> =IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))
>>
>> Great!  A whole new way of doing things that I'd never seen before.
>> Another formula to go into my TIPS folder <g>.
>>
>> I actually only ran into a couple of challenges, any way to not have the
>> 2nd result for "Neither" as $0.00, not show up?  I'd like to be able to
>> choose that "Neither" option, but I think I'd prefer that the cell remain
>> blank and not show up as $0.00.
>>
>> Also, when no text string is chosen and the cell is just blank, a #N/A
>> appears in E2.  Can you recommend how to handle this one, by any chance?
>>
>> Thanks much!  This is great.  :oD
>>
>>> -- 
>>> HTH
>>>
>>> Bob
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>> "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message
>>> news:ufBduTeBIHA.3564@TK2MSFTNGP04.phx.gbl...
>>>>I have this formula that goes into E2 in the first line:
>>>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
>>>> which works if the text string PST+GST is found in D2.  But I don't
>>>> know how to branch out from there.  There are 4 possible answers to go
>>>> into D2 which will affect outcome of E2.  They are, and I'll list them
>>>> all including the PST+GST case above:
>>>>
>>>> if D2 = text string "PST+GST", E2 = SUMPRODUCT(C2*0.13)
>>>> if D2 = text string "PST", E2 = SUMPRODUCT(C2*0.06)
>>>> if D2 = text string "GST", E2 = SUMPRODUCT(C2*0.06)    [currently both
>>>> taxes are equal where for years they were 0.06 and 0.07]
>>>> if D2 = text string "Neither", E2 = nothing, no change to C2
>>>>
>>>> I'm using the above formula because that's what I know more or less how
>>>> to do.  But there might be something better (?).
>>>>
>>>> Also, when playing around with figures, I have had some FALSE and
>>>> #VALUE appear, so hoping to cross that bridge when I get to it. <g>
>>>>
>>>> Thanks!  :oD
>>>>
>>>>
>>>
>>>
>>
>>
>
>






0
NoSpam5247 (64)
10/5/2007 1:39:35 PM
Reply:

Similar Artilces:

Calculated Fields
Is there a way to use an IF or SUMIF function in a calculated field in a pivot table? When I try, I get all 0s as the result. Can you type a small sample of the pivot table layout and data, and explain what you'd like to calculate with a SUMIF formula? Jim C. wrote: > Is there a way to use an IF or SUMIF function in a > calculated field in a pivot table? When I try, I get all > 0s as the result. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Hi Jim, > Is there a way to use an IF or SUMIF function in a > calculated f...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

Money using wrong date to calculate ROI YTD
I have opened a new file in Money to track investments for a stock club. I entered all transactions, which go back to Jan. 2002. When I look at performance for the account, the ROI YTD is not accurate, as Money is using values for the stocks going back only 6 months form the date I enterd the transactions rather than the values as of 1/1/03. What to do? In microsoft.public.money, Brooks wrote: >I have opened a new file in Money to track investments for >a stock club. I entered all transactions, which go back >to Jan. 2002. When I look at performance for the account, &g...

Analytical Accounting #4
Has anyone encounter issue with multilevel query with more than 3 accounting class? and how you found Analytical Accounting in general? Thanks kanti ...

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

If/then to print text.
I want to use a formula so that when a value over zero is put into one cell, a line of text prints in another cell. I know it is an if then function, but I've forgotten the exact formula and the way to encode the text message in the formula. Shadyhosta wrote: > I want to use a formula so that when a value over zero is put into one cell, > a line of text prints in another cell. I know it is an if then function, but > I've forgotten the exact formula and the way to encode the text message in > the formula. Try this: =IF(A1>0,"print this text","prin...

using A5 paper as a booklet in Publisher 2007 i.e 4 A6 sides
In 2003 I made greeting cards on A5 card by using the booklet I cannot make this work in 2007 -- mumbles star Use one of the Avery A4/A5 templates. C9355 works... If you use the A5 folded down to A6 it makes a small publication. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "mumbles" <mumbles@discussions.microsoft.com> wrote in message news:AA578767-48DF-4896-8E8C-FD17ECAEB500@microsoft.com... > In 2003 I made greeting cards on A5 card by using the booklet I cannot make > this work in 2007 > -- > mum...

Validation list based on criteria
Hi I want to create a data validation list based on certain conditions. Let me try to explain by example. A1 = oranges B1 = oranges A2 = apples B2 = no record A3 = pears B3 = pears A4 = pears B4 = pears A5 = grapes B5 = grapes A6 = apples B6 = no record B:B value is based on a relative formula determined by the value in A A:A can have duplicate values I want to create a validation list based on B:B where the formula result is “no record” The list needs to be populated with the relative value in A For example: Since B2 and B6 = “no record” then the validat...

Unable to calculate STD DEV for any value with 6 or more digits to the left dec.
I am unable to obtain the standard deviation for any value with six or more digits to the left of the decimal. The values are coordinates, usually with six or seven digits to the left of the decimal and three to the right. Found four types of standard deviation in the Microsoft Excel help menus. 1) STDEV - will work with values up to five places left of the decimal point. 2) STDEVA - will work with values up to five places left of the decimal point. 3) STDEVP - will work with values up to five places left of the decimal point. 4) STDEVPA - will work with values up to five places left of...

HELP!!! #4
My computer crashed... I'm recovering my files from my hard drive, but I never did backups to save my email. I had thousands of emails in Outlook... is there any way to retrieve those? By the way, I had to connect my old hard drive to another PC and access my hard drive in that manner. >-----Original Message----- >My computer crashed... I'm recovering my files from my >hard drive, but I never did backups to save my email. I >had thousands of emails in Outlook... is there any way to >retrieve those? >. > Look for file(s) with a *.PST extension. Location...

Text color
Hi All, When I open windows explorer and click on any word or folder way on the left , it is highlited in a very light blue, how can I change that color to say like dark blue like windows classic . Thank you. Carl G Hi, You would have to revert to a classic theme and then change the color for "selected items" in the advanced color properties of Personalization/Windows Color and Appearance. It's not customizable when running an aero theme. -- Best of Luck, Rick Rogers, aka "Nutcase" - Microsoft MVP http://mvp.support.microsoft.com/ Windows he...

Full-Text Indexing #3
Is there a way to enable full-text indexing in an outlook client for Public Folders when the client is working offline? ...

Creating a chart with text not numbers
How do I create a chart that has text values (freshman, sophomore, junior, senior) instead of numbers (1, 2, 3, 4)? I have a column labeled "What is your class standing?" and below it is 166 rows where people selected "Freshman," "Sophomore," etc. I want to create a bar chart that counts how many people selected each of the four classes. Thanks, Alan First of all, you will have numbers to plot. The words are your category labels. Read how to turn a column of words like this into a histogram: http://peltiertech.com/Excel/Charts/Histograms.html - Jon...

on_change() event for text fields
Is there really no way to assign an event to a text field? We want to be able to have the format saved correctly for phone numbers when they're entered in (###)###-#### I've read some threads about this already and it looks like it cannot be done unless you write an app to constantly check and replace the phone numbers within the database itself. I can do this but it would be nice to know if there was any simpler way of going about this. Thanks in advance. nope, not at all,.....only picklists its kinda stupid too because under text boxes they have the ability to select &qu...

Pasting email address into text field
Hello all, I am attempting to copy/paste several rows of data from an excel spreadsheet into an Access form. I receive an error: "The value you have entered into this field is invalid" when the data contains an email address. The email address field is set for text with a field length of 255 characters in the DB. I can copy the individual email address into the field without any problems. the problem occurs only when I am copying the whole row or rows of data from the excel spreadsheet into the Access DB. Any ideas about getting around this problem? Hi, this is exactly wh...

Help with "base unit" in chart axis
I have two charts. In one, when I go to "format axis" I can enter the min and max scale as dates and the entry remains as dates when I come back. I can also set the "base unit" a major and minor grid line in days, weeks, months, etc. The second chart, I do not have a base unit drop down box, and although I can enter dates in the max and min in date format and they work, when I come back the entries are in days since the start of time (38353 in 1/1/2005) And I cannot specify the units of major and minor grid lines, they are just units. Which makes it awkward for minor gi...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Resource Units
I want to be able to have a resource, say 'electrician', with 5 available for the day shift and 2 available for the night shift. This would allow e to just assign the 'electrician' resource and have project level based on unit availability. If I setup 'electrician Day' and 'electrician Night', then I need to carefully select which to use and if the schedule shifts it is a nightmare. Any thought on how to do this? Thank-you! On Jan 6, 10:43=A0pm, John K <John K...@discussions.microsoft.com> wrote: > I want to be able to have a resou...

Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and deposits. Current balance is determined by subtracting withdrawals and adding deposits to the balance in the line above. Suddenly I have started getting a cell entry #VALUE! in the current balance cell. A typical entry in the cell is for G43 would be: =G42-E43+F43 and the sudden new result is #VALUE. All three columns have cells formatted as numbers. HELP! On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote: > I have a spreadsheet with three columns: Current balance, withdrawals, and > deposit...

howto: convert from xml to text ?
Hi, If anyone knows how to convert a input xml (getting from a url querry) and convert it into a flat, csv text file ?? Thanks, Gopi hmm... but can you point some examples...?? Thanks, Gopi "Dimitre Novatchev" <dnovatchev@yahoo.com> wrote in message news:be7d6s$235oo$1@ID-152440.news.dfncis.de... > This can be done using XSLT. > > > ===== > Cheers, > > Dimitre Novatchev. > http://fxsl.sourceforge.net/ -- the home of FXSL > > > > "Gopinath Varadharajan" <vgopinath@hotmail.com> wrote in message > news:exXDxHzQDHA.402...

msg template. Text sent not what typed!
Hi I have had the strangest issue with Outlook. I sometimes use msg files to send emails to colleagues. Normally I have a template set up and I may change the to, cc, subject and body of the email slightly to reflect the topic of the email. However the bulk of the email remains the same. The other day I was sending an email and had amended the template (not saved as need to use the template again), and sent it out. Whilst the to,cc and subject fields were correct the text i had entered in the body of the email had gone and other text had been entered and my signature had dissappea...

Inventory #4
The item shows 4 in stock 4 available 0 allocated 0 everything else. When I try to put 4 on a sales order it shows 4 in inventory 3 available; I found 1 at another location I have run inventory reconcile, 0 errors on the report. There no other sales with this item on an order, only on a quote. This is where it gets odd. I varianced the 4 items into stock. ( I have a printout it says 4) where would I look to see where the missing item would be? Thanks, Pat -- P McCarthy Dear, Did you validate the site? The item may be distributed on two sites, one holds the three pieces and another one ...

Loading PivotCache directly from a text file
According to the Excel online help, the PivotCache.Connection property can be set to "the path to and file name of a text file". I tried setting it to the path and file name of a .csv file and it returned an error. Is there a way to load a PivotCache directly from a flat file without having to load into a spreadsheet or database? (The flat file has more than 1 million rows.) -- Tom Sherwood ...

text styles
in MP 2007- I have selected all columns, all rows. I cleared all formats. I go to set text styles. Set all items to automatic then, I set critical path items to Fuchsia or lime and OK... these tasks are still black (automatic) and will not retain that fuchsia setting. My 2003 schedules this works great! Is fuchsia just not working as a color in 2007? Huh, that would appear to be a bug. Red works fine. Fuschia doesn't seem to stick for some reason. I can replicate that on my machine with no difficulty. I guess you should avoid fuschia. It's such a 90s color anywa...

Extend text to next cell
I am trying to get my text to run over to the next cell only in display not actual value of that cell. So if A1 says, "How now brown cow.", it should show over cells A2 and A3. However, I also need the carriage returns to be picked up. If I use the "Wrap Text" property it only shows the sentence in that cell. If I don't use it I get the [] (brackets) in place of the carriage return. Maybe you could merge those cells (A1:A3 or did you really mean A1:C1?) But be aware that merging cells causes trouble with sorting, pasting, and lots of other stuff. Format|cells|al...