Formula wont work

Copper 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"  .