Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge for Anyone to take it on.
I want a Formula that will answer the following.
EG: 110 110 124 = 344 D The =sum 344 part i have done.. Problem i have is the following: I want a function that will find it between a range of numbers and give a letter answer. For Example if answer is between 345:404 answer is B 285:344 answer is C 225:284 answer is D 184:224 answer is E The next part is I need the formula to be able to change if i change the sum. So if the sum answer is changed and the new answer is 345 for example it would now be B now and not C. Please Help.... ANYONE FROM SO CONFUSED |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge for Anyone to take it on.
Assuming you don't have too many possibilities, you could use a LOOKUP in
your IF function. The thing to remember about LOOKUP, is always have your values increase from low to high.... For example, with the data you gave: =LOOKUP(SUM(A1:A10),{184,"E";225,"D";285,"C";345," B"}) This will, however, return an error if your sum is less than 184, and it will return "B" for any value 345 or higher. Not sure how you want those handled, so would need to add in additional handling for that. Say, for example, that anything 405 and higher is "A", and anything less than 184, you want just blank, you could do: =IF(SUM(A1:A10)<184,"",LOOKUP(SUM(A1:A10),{184,"E" ;225,"D";285,"C";345,"B";405,"A"}) If, however, you have a multitude of ranges that you want to test for, you may want to build a table and use VLOOKUP. For example, on Sheet2, type the following in cells A2 to A7: 0 | 184 | 225 | 285 | 345 | 405 leave B2 blank or "F", or whatever value you want for the range 0-183 in B2 B3 to B7: E | D | C | B | A Then, your formula would be: =VLOOKUP(SUM(A1:A10),Sheet2!$A$2:$B$7,2,TRUE) Again, assuming you have error handling for your main range A1 to A10 (to avoid text entries or invalid entries, and allow only positive entries). Hope this helps. -- ** John C ** "SO CONFUSED" wrote: I want a Formula that will answer the following. EG: 110 110 124 = 344 D The =sum 344 part i have done.. Problem i have is the following: I want a function that will find it between a range of numbers and give a letter answer. For Example if answer is between 345:404 answer is B 285:344 answer is C 225:284 answer is D 184:224 answer is E The next part is I need the formula to be able to change if i change the sum. So if the sum answer is changed and the new answer is 345 for example it would now be B now and not C. Please Help.... ANYONE FROM SO CONFUSED |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge for Anyone to take it on.
What if the sum is above 404 or below 184?
-- Biff Microsoft Excel MVP "SO CONFUSED" <SO wrote in message ... I want a Formula that will answer the following. EG: 110 110 124 = 344 D The =sum 344 part i have done.. Problem i have is the following: I want a function that will find it between a range of numbers and give a letter answer. For Example if answer is between 345:404 answer is B 285:344 answer is C 225:284 answer is D 184:224 answer is E The next part is I need the formula to be able to change if i change the sum. So if the sum answer is changed and the new answer is 345 for example it would now be B now and not C. Please Help.... ANYONE FROM SO CONFUSED |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge for Anyone to take it on.
Hi,
In a separate range (G11:I15), enter the following 184 224 E 225 284 D 285 344 C 345 404 B Now you can use the following formula =vlookup(sum(A1:A3),G11:I15,3) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SO CONFUSED" <SO wrote in message ... I want a Formula that will answer the following. EG: 110 110 124 = 344 D The =sum 344 part i have done.. Problem i have is the following: I want a function that will find it between a range of numbers and give a letter answer. For Example if answer is between 345:404 answer is B 285:344 answer is C 225:284 answer is D 184:224 answer is E The next part is I need the formula to be able to change if i change the sum. So if the sum answer is changed and the new answer is 345 for example it would now be B now and not C. Please Help.... ANYONE FROM SO CONFUSED |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge for Anyone to take it on.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A challenge | New Users to Excel | |||
Challenge | Excel Worksheet Functions | |||
A Challenge | Excel Discussion (Misc queries) | |||
A Challenge | Excel Worksheet Functions | |||
Who is up for a challenge? | Excel Discussion (Misc queries) |