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

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


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=530502

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


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=530502

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

=IF(C22="x",B22*80*1.5,IF(B220,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

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

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.


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=530502