Need help with a function
 

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!

Need help with a function
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Doug" wrote:

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!

Need help with a function
 

On Jan 28, 5:00*pm, Doug wrote:
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%, "")

Need help with a function
 

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

--
Biff
Microsoft Excel MVP


"Doug" wrote in message
...
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!