Populating a Cell based on a Range of Values (Part II)

  • Thread starter Thread starter akkrug
  • Start date Start date
A

akkrug

Sorry, but my I'm still not sure how to make my formula evaluate a full range
of values. For example, the cell I need to populate will have values based
on what is calculated in another cell as follows:
If value in cell A1 is 1.0 and > 0 in new cell
.900 - .999 1 in new cell
.800 - .899 2 in new cell
all the way down to <.100 which should put a 10 in the new cell.

Thanks again for all the help! It is definitely a learning experience for
me.

Ken
 
Part of the formula is working:
=IF(AND(A1>=0.8,A1<=0.899),2)

I'm not sure how to get the rest of the values in though (i.e. if A1 >=.7
and <.799, the valued in cell change from 2 to a 3.
 
You could create a separate datalist, and reference it with a Lookup
formula.
OR
You could include the parameters within the formula itself.

Separate datalist:

In M1 to M11:
0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1

In N1 to N11:
10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0

Formula:
=LOOKUP(A1,M1:N11)


Parameters within the formula:

=LOOKUP(A1,{0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1;10,9,8,7,6,5,4,3,2,1,0})


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Sorry, but my I'm still not sure how to make my formula evaluate a full
range
of values. For example, the cell I need to populate will have values based
on what is calculated in another cell as follows:
If value in cell A1 is 1.0 and > 0 in new cell
.900 - .999 1 in new cell
.800 - .899 2 in new cell
all the way down to <.100 which should put a 10 in the new cell.

Thanks again for all the help! It is definitely a learning experience for
me.

Ken
 
I'm still not sure how to evaluate each of the ranges (from .900 through .999
should generate a value of 1. from .800 through .899 should generate a 2,
..700 through .799 should generate a 3). Guess I'm just not really good at
math!

Ken K. - 2191
 
That's *exactly* what my suggestions return!

Have you tried them?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I'm still not sure how to evaluate each of the ranges (from .900 through
..999
should generate a value of 1. from .800 through .899 should generate a 2,
..700 through .799 should generate a 3). Guess I'm just not really good at
math!

Ken K. - 2191
 
I just tried them and they work beautifully!! Thanks for your help ( and
patience). It's obviously been a long time since I've done anything in
Excel.

Ken K.
 
Back
Top