Forum Discussion

cowleyp's avatar
cowleyp
Copper Contributor
Mar 24, 2020

Help with an IF/AND formula please

Hi there, 

 

My formula is 

=IF(AND(P88>0,O88="T1"),P88*data!$E$16,IF(O88="T1.5",P88*data!$E$17,IF(O88="T2",P88*data!$E$18,)))

 

where the first bit says if P88 is greater than zero AND 088 = T1, then multiply by P88 on my worksheet called data.

 

T1 = my pay rate for single time

T1.5 = my pay rate for time and half

T2 = my pay rate for double time.

 

So if my P88 (hours worked) is greater than zero - multiply it by the T rate.

 

The problem I have, is if the hours worked is blank, but O88 is set to any of the T's (T1, T1.5, T2), then the cell reports #VALUE!

 

I want to be able to select the T rate in column O88, and with no hours entered in P88, the result is a blank cell.... because P88>0 should overrule everything.

 

Any help - much appreciated

 

Thank you

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    cowleyp You can wrap your entire formula in an IFERROR statement.

    =IFERROR(<formula>,"")

     In your case it becomes like this:

    =IFERROR(IF(AND(P88>0,O88="T1"),P88*data!$E$16,IF(O88="T1.5",P88*data!$E$17,IF(O88="T2",P88*data!$E$18,))),"")

     

    • cowleyp's avatar
      cowleyp
      Copper Contributor

      Riny_van_Eekelen 

       

      Hi Riny, 

      Thanks for your quick and accurate help!

       

      Your suggestion worked perfectly! SOLVED

       

      Thanks

       

Resources