Help with an IF/AND formula please

%3CLINGO-SUB%20id%3D%22lingo-sub-1248425%22%20slang%3D%22en-US%22%3EHelp%20with%20an%20IF%2FAND%20formula%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1248425%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(P88%26gt%3B0%2CO88%3D%22T1%22)%2CP88*data!%24E%2416%2CIF(O88%3D%22T1.5%22%2CP88*data!%24E%2417%2CIF(O88%3D%22T2%22%2CP88*data!%24E%2418%2C)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20the%20first%20bit%20says%20if%20P88%20is%20greater%20than%20zero%20AND%20088%20%3D%20T1%2C%20then%20multiply%20by%20P88%20on%20my%20worksheet%20called%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ET1%20%3D%20my%20pay%20rate%20for%20single%20time%3C%2FP%3E%3CP%3ET1.5%20%3D%20my%20pay%20rate%20for%20time%20and%20half%3C%2FP%3E%3CP%3ET2%20%3D%20my%20pay%20rate%20for%20double%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20my%20P88%20(hours%20worked)%20is%20greater%20than%20zero%20-%20multiply%20it%20by%20the%20T%20rate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I%20have%2C%20is%20if%20the%20hours%20worked%20is%20blank%2C%20but%20O88%20is%20set%20to%20any%20of%20the%20T's%20(T1%2C%20T1.5%2C%20T2)%2C%20then%20the%20cell%20reports%20%23VALUE!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20select%20the%20T%20rate%20in%20column%20O88%2C%20and%20with%20no%20hours%20entered%20in%20P88%2C%20the%20result%20is%20a%20blank%20cell....%20because%26nbsp%3BP88%26gt%3B0%20should%20overrule%20everything.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20-%20much%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1248425%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1248550%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20an%20IF%2FAND%20formula%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1248550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F592089%22%20target%3D%22_blank%22%3E%40cowleyp%3C%2FA%3E%26nbsp%3BYou%20can%20wrap%20your%20entire%20formula%20in%20an%20IFERROR%20statement.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%3CFORMULA%3E%2C%22%22)%3C%2FFORMULA%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BIn%20your%20case%20it%20becomes%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(IF(AND(P88%26gt%3B0%2CO88%3D%22T1%22)%2CP88*data!%24E%2416%2CIF(O88%3D%22T1.5%22%2CP88*data!%24E%2417%2CIF(O88%3D%22T2%22%2CP88*data!%24E%2418%2C)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1250825%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20an%20IF%2FAND%20formula%20please%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20quick%20and%20accurate%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20suggestion%20worked%20perfectly!%20SOLVED%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

@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,))),"")

 

Highlighted

@Riny_van_Eekelen 

 

Hi Riny, 

Thanks for your quick and accurate help!

 

Your suggestion worked perfectly! SOLVED

 

Thanks