Formula wont work

%3CLINGO-SUB%20id%3D%22lingo-sub-2959172%22%20slang%3D%22en-US%22%3EFormula%20wont%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959172%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20tell%20me%20why%20this%20formula%20wont%20work%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dif(F2%26lt%3B7%2CE2%26lt%3B%24%2CH2%3AJ2%3DQ2%2CO2%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2959172%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2959196%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20wont%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216691%22%20target%3D%22_blank%22%3E%40DesignDiva%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3Dif(F2%26lt%3B7%2CE2%26lt%3B%24%2CH2%3AJ2%3DQ2%2CO2%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20you%20attach%20a%20file%20in%20which%20the%20formula%20is%20entered%20and%20where%20it%20is%20shown%20what%20you%20want%20to%20do%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EE2%26lt%3B%24%26nbsp%3B%20%26nbsp%3B%20This%20expression%20can't%20be%20applied%20because%20the%20value%20of%20E2%20can't%20be%20smaller%20than%20the%20dollar%20sign.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EH2%3AJ2%3DQ2%26nbsp%3B%20%26nbsp%3B%20Do%20you%20want%20the%20sum%20of%20cells%20H2%3AJ2%20to%20be%20equal%20to%20the%20value%20of%20cell%20Q2%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EO2%22%22%26nbsp%3B%20%26nbsp%3BDo%20you%20want%20the%20cell%20O2%20to%20be%20empty%20if%20all%20the%20statements%20are%20true%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2959199%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20wont%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216691%22%20target%3D%22_blank%22%3E%40DesignDiva%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3Dif(F2%26lt%3B7%2CE2%26lt%3B%24%2CH2%3AJ2%3DQ2%2CO2%22%22)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EE2%26lt%3B%24%20dollar%20is%20not%20the%20text%20and%20not%20number.%20E2%26lt%3B%22%24%22%20has%20no%20sense%2C%20perhaps%20E2%26lt%3B100%20or%20like%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EH2%3AJ2%3DQ2%20if%20you%20Excel%20support%20dynamic%20arrays%20it%20returns%20an%20array%20of%20TRUE%20and%20FALSE%20depends%20on%20which%20values%20are%20in%20the%20cells.%20Most%20probably%20that's%20not%20you%20take%20in%20mind%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EO2%22%22%20-%20what's%20that%3F%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EPerhaps%20you%20may%20describe%20desired%20logic%20of%20the%20formula%2C%20that%20will%20be%20easier%26nbsp%3Bto%20suggest%20workable%20variant%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2959210%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20wont%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2959307%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20wont%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2959307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216691%22%20target%3D%22_blank%22%3E%40DesignDiva%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(%24F2%26lt%3B7%2C%24E2%3D%24P%242%2CH2%3D%24Q%242)%2C%24O%242%2CIF(AND(%24F2%26lt%3B7%2C%24E2%3D%24P%243%2CH2%3D%24Q%243)%2C%24O%243%2CIF(AND(%24F2%26lt%3B7%2C%24E2%3D%24P%246)%2C%24O%246%2C%22%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20entered%20the%20above%20formula%20to%20choose%20between%20Priority1%2C%20Priority2%20and%20Priority3%20depending%20on%20%24%2C%20%24%24%2C%20%24%24%24%2C%20%22Yes%22%20and%20%22No%22%20and%20Days%20to%20implement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20want%20Priority2%20in%20case%20of%20%24%24%20and%20%22No%22%3F%20I%20entered%20formula%20according%20to%20information%20in%20range%20O2%3AQ6%20of%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Can someone tell me why this formula wont work?

 

=if(F2<7,E2<$,H2:J2=Q2,O2"")

6 Replies

@DesignDiva 

=if(F2<7,E2<$,H2:J2=Q2,O2"")

 

Can you attach a file in which the formula is entered and where it is shown what you want to do?

 

E2<$    This expression can't be applied because the value of E2 can't be smaller than the dollar sign.

 

H2:J2=Q2    Do you want the sum of cells H2:J2 to be equal to the value of cell Q2?

 

O2""   Do you want the cell O2 to be empty if all the statements are true?

@DesignDiva 

=if(F2<7,E2<$,H2:J2=Q2,O2"")

E2<$ dollar is not the text and not number. E2<"$" has no sense, perhaps E2<100 or like

H2:J2=Q2 if you Excel support dynamic arrays it returns an array of TRUE and FALSE depends on which values are in the cells. Most probably that's not you take in mind

O2"" - what's that?

 

Perhaps you may describe desired logic of the formula, that will be easier to suggest workable variant

@DesignDiva 

=IF(AND($F2<7,$E2=$P$2,H2=$Q$2),$O$2,IF(AND($F2<7,$E2=$P$3,H2=$Q$3),$O$3,IF(AND($F2<7,$E2=$P$6),$O$6,"")))

 

I entered the above formula to choose between Priority1, Priority2 and Priority3 depending on $, $$, $$$, "Yes" and "No" and Days to implement.

 

Do you want Priority2 in case of $$ and "No"? I entered formula according to information in range O2:Q6 of the sheet.

The O2:Q6 was just me playing around with the formulas... Can you share one that does not have the O2:Q6 data...

@DesignDiva 

=IF(AND($F2<7,$E2="$",H2="Yes"),"Priority1",IF(AND($F2<7,$E2="$$",H2="No"),"Priority2",IF(AND($F2<7,$E2="$$$"),"Priority3","")))

 

I removed everything from range O2:Q6 in the formula but i wonder what formula should return e.g. if   $F2<7,$E2="$",H2="Yes"   or     $F2<7,$E2="$$",J2="No"  .