Challenge for Anyone to take it on.

[SO CONFUSED]

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

 

[John C]

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.

 

[T. Valko]

What if the sum is above 404 or below 184?

 

[Ashish Mathur]

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

 

[Ron Rosenfeld]

On Tue, 11 Nov 2008 19:58:00 -0800, SO CONFUSED <SO

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

Use a table and the VLOOKUP function.

For example:

Set up a table some place on your worksheet:

0 unspecified
184 E
225 D
285 C
344 B
405 unspecified

NOTE the entries in the cells next to the 0 and the 405. Since you did not
specify what you wanted returned in those instances, the word "unspecified"
will be returned. But you can easily change this.

If your table is in C16; and your data is in A1:A3, then the formula would
be:

=VLOOKUP(SUM(A1:A3),C16,2)


--ron