software

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 quot;xquot; 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=quot;quot;,0,IF(C22=quot;xquot;,B22*80*1.5,IF(C22lt;gt;quot;xquot;,MAX ((B22*80),18.75))))
The problem is, if someone places a 0 in B22 and there is no quot;xquot; 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(C22lt;gt;quot;xquot;,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: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=530502
=IF(OR(ISBLANK(B22),B22=0),0,IF(C22=quot;xquot;,B22*80*1.5 ,IF(C22lt;gt;quot;xquot;,MAX((B22*80),18.75))))--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: www.excelforum.com/member.php...foamp;userid=3472
View this thread: www.excelforum.com/showthread...hreadid=530502
=IF(OR(ISBLANK(B22),B22=0),0,IF(C22=quot;xquot;,B22*80*1.5 ,IF(C22lt;gt;quot;xquot;,MAX((B22*80),18.75))))--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: www.excelforum.com/member.php...foamp;userid=3472
View this thread: www.excelforum.com/showthread...hreadid=530502Try this..........

=IF(C22=quot;xquot;,B22*80*1.5,IF(B22gt;0,MAX((B22*80),18.75 ),quot;quot;))

Vaya con Dios,
Chuck, CABGx3
quot;pdgoodquot; wrote:

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

Please try this one: if I interpreted your post and formula correctly,
this should do it for you.
=IF(B22=0,0,IF(C22=quot;xquot;,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 quot;xquot; in C22. The
other two solutions return a 0. All of those will work for me. Many
thanks.--
pdgood
------------------------------------------------------------------------
pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=530502