Multiple ifs

[pdgood]

I seem to struggle with multiple ifs. I'm creating a pricing sheet. If the value of B22 (time spent on the job) is 0 (or blank) then the answer (in D22) should be 0. If not, then the answer should be whichever is greater: B22*80 (charge per hour) or 18.75. That much is easy. But when overtime is considered then it gets more complex. I have made C22 be the overtime cell and if an "x" is placed in that cell then the answer needs to be multiplied by 1.5.
I was able to get all that working with:
=IF(B22="",0,IF(C22="x",B22*80*1.5,IF(C22<>"x",MAX((B22*80),18.75))))
The problem is, if someone places a 0 in B22 and there is no "x" then I'm returning a charge of $18.75 but it should be 0.
So I tried to see if I could do a lookup (I admit I don't know what I'm doing here) and ran this as a test
=IF(C22<>"x",LOOKUP(B22,{0,0.001},{0,MAX((B22*80),18.75)}))
But this returned an error.
I'm open to any way to accomplish this including ways I have not tried.
Thanks.

[MattShoreson]

=IF(OR(ISBLANK(B22),B22=0),0,IF(C22="x",B22*80*1.5,IF(C22<>"x",MAX((B22*80),18.75))))

[CLR]

Try this..........

=IF(C22="x",B22*80*1.5,IF(B22>0,MAX((B22*80),18.75),""))

Vaya con Dios,
Chuck, CABGx3



"pdgood" wrote:

>
> I seem to struggle with multiple ifs. I'm creating a pricing sheet. If
> the value of B22 (time spent on the job) is 0 (or blank) then the
> answer (in D22) should be 0. If not, then the answer should be
> whichever is greater: B22*80 (charge per hour) or 18.75. That much is
> easy. But when overtime is considered then it gets more complex. I have
> made C22 be the overtime cell and if an "x" is placed in that cell then
> the answer needs to be multiplied by 1.5.
> I was able to get all that working with:
> =IF(B22="",0,IF(C22="x",B22*80*1.5,IF(C22<>"x",MAX((B22*80),18.75))))
> The problem is, if someone places a 0 in B22 and there is no "x" then
> I'm returning a charge of $18.75 but it should be 0.
> So I tried to see if I could do a lookup (I admit I don't know what I'm
> doing here) and ran this as a test
> =IF(C22<>"x",LOOKUP(B22,{0,0.001},{0,MAX((B22*80),18.75)}))
> But this returned an error.
> I'm open to any way to accomplish this including ways I have not
> tried.
> Thanks.
>
>
> --
> pdgood
> ------------------------------------------------------------------------
> pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
> View this thread: http://www.excelforum.com/showthread...hreadid=530502
>
>

[Dave O]

Please try this one: if I interpreted your post and formula correctly,
this should do it for you.
=IF(B22=0,0,IF(C22="x",B22*80*1.5,MAX(B22*80,18.75)))

[pdgood]

Thanks! All three solutions work. The only difference is that the second solution returns a blank if B22 is 0 and there is no "x" in C22. The other two solutions return a 0. All of those will work for me. Many thanks.