Calculations based on 4 possible text strings?

[StargateFanFromWork]

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

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

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

 

[StargateFanFromWork]

Thanks, Sandy!! 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 address removed)
Replace @mailinator.com with @tiscali.co.uk

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

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

 

[StargateFanFromWork]

=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)

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]

=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)

=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)

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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

 

[StargateFanFromWork]

=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.

--
HTH

Bob

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

=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)

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

 

[StargateFanFromWork]

=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.

--
HTH

Bob

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

=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)

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