Calculations based on 4 possible text strings?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFanFromWork - 03 Oct 2007 18:43 GMT

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

Reply to this Message

Sandy Mann - 03 Oct 2007 19:04 GMT

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,""))))

Signature

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

>I have this formula that goes into E2 in the first line:
> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
[quoted text clipped - 16 lines]
>
> Thanks! :oD

Reply to this Message

Sandy Mann - 03 Oct 2007 19:11 GMT

Ooops forgot Neither but Bob's formula is better.

Signature

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

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

> Assuming that the taxes will at some time be different again try:
>
[quoted text clipped - 20 lines]
>>
>> Thanks! :oD

Reply to this Message

StargateFanFromWork - 03 Oct 2007 19:52 GMT

Thanks, Sandy!! :oD

> Ooops forgot Neither but Bob's formula is better.
>
[quoted text clipped - 22 lines]
>>>
>>> Thanks! :oD

Reply to this Message

Bob Phillips - 03 Oct 2007 19:06 GMT

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

Signature

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),""))
[quoted text clipped - 16 lines]
>
> Thanks! :oD

Reply to this Message

StargateFanFromWork - 03 Oct 2007 20:01 GMT

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

>>I have this formula that goes into E2 in the first line:
>> =IF(C2<>"",IF(D2="PST+GST",SUMPRODUCT(C2*0.13),""))
[quoted text clipped - 16 lines]
>>
>> Thanks! :oD

Reply to this Message

Bob Phillips - 04 Oct 2007 00:32 GMT

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

Signature

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}))
>
[quoted text clipped - 31 lines]
>>>
>>> Thanks! :oD

Reply to this Message

StargateFanFromWork - 05 Oct 2007 14:15 GMT

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

>>> =IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))
>>
[quoted text clipped - 31 lines]
>>>>
>>>> Thanks! :oD

Reply to this Message

StargateFanFromWork - 05 Oct 2007 14:39 GMT

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

>>> =IF(C2="","",C2*LOOKUP(D2,{"GST","Neither","PST","PST+GST"},{0.06,0,0.06,0.13}))
>>
[quoted text clipped - 31 lines]
>>>>
>>>> Thanks! :oD

Reply to this Message

Rick Rothstein (MVP - VB) - 04 Oct 2007 07:18 GMT

> 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

Reply to this Message

Bob Phillips - 04 Oct 2007 09:04 GMT

That returns 0.12 for PST+GST, whereas OP wanted 0.13

Signature

HTH

Bob

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

>> Great! A whole new way of doing things that I'd never seen before.
>
[quoted text clipped - 17 lines]
>
> Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 04 Oct 2007 09:48 GMT

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

> That returns 0.12 for PST+GST, whereas OP wanted 0.13
>
[quoted text clipped - 19 lines]
>>
>> Rick

Reply to this Message

Rate this thread: