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