Need help with a function

[Doug]

I want to have Excel give back a certain value if it meets some criteria.

Example: If the value of a cell is between 0 & 5%, then give back 10%.
If the value of a cell is between 6 & 10%, then give back 9%.

thx for your help!

 

[Shane Devenshire]

Hi,

Best solution is VLOOKUP or similar function

Make a table with the something like this
0 10%
6% 9%

=VLOOKUP(A1,B$1:C$3,2)

Assuming the table is in B1:C3 and the value you want checked is in A1.

 

[joeu2004]

I want to have Excel give back a certain value if it meets
some criteria. Example:  If the value of a cell is between
0 & 5%, then give back 10%. If the value of a cell is between
6 & 10%, then give back 9%.

And if neither pair of conditions is met, ...?

=if(and(0<=A1,A1<=5%), 10%, if(and(6%<=A1,A1<=10%), 9%, "")

That formula does exactly what you ask for. But there is much wrong
with what you ask for.

1. What if 5%<A1 and A1<6%? What if A1<0 or 9%<A1?

2. When you say "between", do you mean, for example, 0<=A1 and A1<=5%
as I wrote; or do you mean 0<A1 and A1<5%?

3. I assume that "the cell" (A1) does indeed contain a percentage or a
fractional value representing a percentage. Or do you mean: the
value in the cell is some percentage of something that you failed to
mention?

4. Be wary of the difference between displayed value (0, 5%, 6%, 10%)
and the actual value. The displayed value might be rounded
automatically, which might mislead you. In light of that, perhaps you
want:

=if(and(0<=A1,round(A1,2)<=5%), 10%, if(and(6%<=round(A1,2),round(A1,2)
<=10%), 9%, "")

 

[T. Valko]

If you only have a "few" conditions to check for...

=IF(COUNT(A1),IF(A1<=5%,10%,IF(A1<=10%,9%,"")),"")

If you have "many" conditions to check for then you're best option is to
create a lookup table. See this for more detail:

http://contextures.com/xlFunctions02.html