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! D

Ooops forgot Neither but Bob's formula is better.

--

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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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! D
>>
>>
>>
>
>
>

Thanks, Sandy!! D

"Sandy Mann" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Ooops forgot Neither but Bob's formula is better.
>
> --
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Sandy Mann" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> (E-Mail Removed)
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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! D
>>>
>>>
>>>
>>
>>
>>
>
>

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%23O%(E-Mail Removed)...
> =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. D

> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my
> addy)
>
> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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! D
>>
>>
>
>

=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" <(E-Mail Removed)> wrote in message
news:eYgSW$(E-Mail Removed)...
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:%23O%(E-Mail Removed)...
>> =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. D
>
>> --
>> HTH
>>
>> Bob
>>
>> (there's no email, no snail mail, but somewhere should be gmail in my
>> addy)
>>
>> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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! D
>>>
>>>
>>
>>
>
>

> 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

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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>>> 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
>
>

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
> news:eYgSW$(E-Mail Removed)...
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:%23O%(E-Mail Removed)...
>>> =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. D
>>
>>> --
>>> HTH
>>>
>>> Bob
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>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! D
>>>>
>>>>
>>>
>>>
>>
>>
>
>

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =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" <(E-Mail Removed)> wrote in message
> news:eYgSW$(E-Mail Removed)...
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:%23O%(E-Mail Removed)...
>>> =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. D
>>
>>> --
>>> HTH
>>>
>>> Bob
>>>
>>> (there's no email, no snail mail, but somewhere should be gmail in my
>>> addy)
>>>
>>> "StargateFanFromWork" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>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! D
>>>>
>>>>
>>>
>>>
>>
>>
>
>