| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help doing complicated formula
I need Excel to look in Cell F29 and populate (with one numerical value from
F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
You will need to spell that out in more detail - at least for me!
Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
Column B represents a number from 95.0% to 99.5%, with each row a different
number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
it is still confusing. the way i look at it, an IF formula should work.
"mrl" wrote in message ... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
Can you show me, please, how to write the IF formula for this example?
The generosity of your knowledge is greatly appreciated. "Gary" wrote: it is still confusing. the way i look at it, an IF formula should work. "mrl" wrote in message ... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
something like...
=IF(AND(A1=95%,A1<=95.49%),"200%",IF(AND(A1=95.5 %,A1<=95.99%),"170%","")) I am not sure if i understood the references correctly. You can change the references if needed and let me know if it works. Thanks "mrl" wrote in message ... Can you show me, please, how to write the IF formula for this example? The generosity of your knowledge is greatly appreciated. "Gary" wrote: it is still confusing. the way i look at it, an IF formula should work. "mrl" wrote in message ... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
mrl wrote...
Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0% <=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5% <=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). .... . . . Lookup functions at all. I'm guessing that's what needed, not an IF formula? Yes, a lookup formula would seem to be best. =LOOKUP(B3,{0;0.95;0.955;0.96},{"unspecified";2;1. 7;"unspecified"}) and format at 0.0%. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
I will try this formula, and the Lookup formula provided by Harlan - I will
post my results Thank you very much -- The generosity of your knowledge is greatly appreciated. "Gary" wrote: something like... =IF(AND(A1=95%,A1<=95.49%),"200%",IF(AND(A1=95.5 %,A1<=95.99%),"170%","")) I am not sure if i understood the references correctly. You can change the references if needed and let me know if it works. Thanks "mrl" wrote in message ... Can you show me, please, how to write the IF formula for this example? The generosity of your knowledge is greatly appreciated. "Gary" wrote: it is still confusing. the way i look at it, an IF formula should work. "mrl" wrote in message ... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
I will try this one, and the IF formula Gary provided. I will post my
results. Thank you very much -- The generosity of your knowledge is greatly appreciated. "Harlan Grove" wrote: mrl wrote... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0% <=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5% <=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). .... . . . Lookup functions at all. I'm guessing that's what needed, not an IF formula? Yes, a lookup formula would seem to be best. =LOOKUP(B3,{0;0.95;0.955;0.96},{"unspecified";2;1. 7;"unspecified"}) and format at 0.0%. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
YIPPEE - The IF formula worked like a charm! All 10 rows are
self-populating now, dependent on formula result in my designated cell, which will change from week to week. THANK YOU THANK YOU -- The generosity of your knowledge is greatly appreciated. "mrl" wrote: I will try this formula, and the Lookup formula provided by Harlan - I will post my results Thank you very much -- The generosity of your knowledge is greatly appreciated. "Gary" wrote: something like... =IF(AND(A1=95%,A1<=95.49%),"200%",IF(AND(A1=95.5 %,A1<=95.99%),"170%","")) I am not sure if i understood the references correctly. You can change the references if needed and let me know if it works. Thanks "mrl" wrote in message ... Can you show me, please, how to write the IF formula for this example? The generosity of your knowledge is greatly appreciated. "Gary" wrote: it is still confusing. the way i look at it, an IF formula should work. "mrl" wrote in message ... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help doing complicated formula
I am glad it worked.
"mrl" wrote in message ... YIPPEE - The IF formula worked like a charm! All 10 rows are self-populating now, dependent on formula result in my designated cell, which will change from week to week. THANK YOU THANK YOU -- The generosity of your knowledge is greatly appreciated. "mrl" wrote: I will try this formula, and the Lookup formula provided by Harlan - I will post my results Thank you very much -- The generosity of your knowledge is greatly appreciated. "Gary" wrote: something like... =IF(AND(A1=95%,A1<=95.49%),"200%",IF(AND(A1=95.5 %,A1<=95.99%),"170%","")) I am not sure if i understood the references correctly. You can change the references if needed and let me know if it works. Thanks "mrl" wrote in message ... Can you show me, please, how to write the IF formula for this example? The generosity of your knowledge is greatly appreciated. "Gary" wrote: it is still confusing. the way i look at it, an IF formula should work. "mrl" wrote in message ... Column B represents a number from 95.0% to 99.5%, with each row a different number. To the right of Column B I need to have Excel fill in the pre-designated number in the correct row; i.e. =95.0%<=95.49 should result in 200% appearing in H5 (since B5 shows 95%) then =95.5%<=95.99 should result in 170% appearing in H6 (since B6 shows 95.5%). The result of a simple formula, which resides in F29 will determine the percentage rating used above, which is what needs to be either true or false to fit into the ranges as explained above? I know I'm inexperienced in writing a complicated formula, and haven't used Lookup functions at all. I'm guessing that's what needed, not an IF formula? Please help? -- The generosity of your knowledge is greatly appreciated. "Bernard Liengme" wrote: You will need to spell that out in more detail - at least for me! Maybe make up an analogous example. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "mrl" wrote in message ... I need Excel to look in Cell F29 and populate (with one numerical value from F5:F14) only one of the series of cells H5:H14 dependent on results in F29 falling between ranges set in B5:B14. I know the fomula needs to reside in H5:H14, but the rest is over my head. F29 is the result of C27/E27 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated formula | Excel Discussion (Misc queries) | |||
| Complicated Formula - I think | Excel Worksheet Functions | |||
| complicated if then formula...need help | Excel Discussion (Misc queries) | |||
| Complicated formula | Excel Worksheet Functions | |||
| Complicated Formula | Excel Discussion (Misc queries) | |||
Linear Mode
