| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Populating a Cell based on a Range of Values (Part II)
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 -- akkrug |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Populating a Cell based on a Range of Values (Part II)
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. -- akkrug "akkrug" wrote: 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 -- akkrug |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Populating a Cell based on a Range of Values (Part II)
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! ================================================== === "akkrug" wrote in message ... 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 -- akkrug |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Populating a Cell based on a Range of Values (Part II)
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 -- akkrug "RagDyeR" wrote: 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! ================================================== === "akkrug" wrote in message ... 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 -- akkrug |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Populating a Cell based on a Range of Values (Part II)
That's *exactly* what my suggestions return!
Have you tried them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "akkrug" wrote in message ... 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 -- akkrug "RagDyeR" wrote: 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! ================================================== === "akkrug" wrote in message ... 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 -- akkrug |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Populating a Cell based on a Range of Values (Part II)
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. -- akkrug "RagDyeR" wrote: That's *exactly* what my suggestions return! Have you tried them? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "akkrug" wrote in message ... 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 -- akkrug "RagDyeR" wrote: 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! ================================================== === "akkrug" wrote in message ... 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 -- akkrug |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Populating a Cell based on a Range of Values (Part II)
=MAX(0,10-INT(A1*10))
"akkrug" wrote: 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 -- akkrug |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populating a cell based on a range of values | New Users to Excel | |||
| auto populating a cell based on another cell for an invoice | Excel Discussion (Misc queries) | |||
| populating a cell based on another range of cells in excel | Excel Discussion (Misc queries) | |||
| Populating a field based on lookup values | Excel Worksheet Functions | |||
| Populating a table based on values in another table | Excel Worksheet Functions | |||
Hybrid Mode
