IF OR AND BETWEEN...I don't know. Help a noob! (Option Quest: Subtotals)

[JRL]

OK! So the issue of the day IS... I want to make cell (E4) return a number (1 through 20) based on a series or ranges which are entered in cell E9.

The ranges are...

1 to 99 = 1, 100 to 224 = 2, 225 to 274 = 3, 375 to 549 = 4, 550 to 749 = 5, 750 to 974 = 6, 975 to 1224 = 7, 1225 to 1499 = 8, 1500 to 1799 = 9, 1800 to 2124 = 10, 2125 to 2474 = 10, 2475 2849 = 11, 2850 to 3249 = 12, 3250 to 3674 = 13, 3675 to 4124 = 14, 4125 to 4574 = 15, 4575 to 5024 = 16, 5025 to 5474 = 17, 5475 to 5925 = 18, 5925 to 3674 = 19, 3675 to 4124 = 20

Additionally, is there a way to make a running total cell that would kind of subtotal what you have and factor in new numbers you place in a cell? Using the example I am included, I would want to remove the contents of the ALTERATION cell and place +12 and have the new total take into account the old results as well as returning a result of 127, rather then 162 total.

BASE 150
ALTERATION -35
CURRENT TOTAL 115

 

[Joe4]

OK! So the issue of the day IS... I want to make cell (E4) return a number (1 through 20) based on a series or ranges which are entered in cell E9.

The ranges are...

1 to 99 = 1, 100 to 224 = 2, 225 to 274 = 3, 375 to 549 = 4, 550 to 749 = 5, 750 to 974 = 6, 975 to 1224 = 7, 1225 to 1499 = 8, 1500 to 1799 = 9, 1800 to 2124 = 10, 2125 to 2474 = 10, 2475 2849 = 11, 2850 to 3249 = 12, 3250 to 3674 = 13, 3675 to 4124 = 14, 4125 to 4574 = 15, 4575 to 5024 = 16, 5025 to 5474 = 17, 5475 to 5925 = 18, 5925 to 3674 = 19, 3675 to 4124 = 20

Assuming you have no "gaps" in your data, you can use VLOOKUP. The last argument of that function allows you to do it without exact matches. What you want to do is create a lookup table that contains the lower bound of each range, and the value you wish to return, i.e.:

[TABLE="width: 96"]
<TBODY>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]LB
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Value
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]100
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]225
[/TD]
[TD="class: xl63, bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]275
[/TD]
[TD="class: xl63, bgcolor: transparent"]4
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]550
[/TD]
[TD="class: xl63, bgcolor: transparent"]5
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]750
[/TD]
[TD="class: xl63, bgcolor: transparent"]6
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]…
[/TD]
[TD="class: xl63, bgcolor: transparent"]…
[/TD]
[/TR]
</TBODY>[/TABLE]

Then, let's name this range, something like "MyRange".
Now, if we wanted to lookup a value in cell G2, our VLOOKUP formula would look something like:
=VLOOKUP(G2,MyRange,2,1)

For more information on the VLOOKUP function, check out Excel's built-in Help files.

I am not sure I understand the second part of your question.

 

[JRL]

Yeah, I'm not even sure how to explain the other part of my issue... I didn't realize that VLOOKUP would assume that there are ranges between the information some how. Good to know. Thank you for the assist!

 

[Joe4]

I didn't realize that VLOOKUP would assume that there are ranges between the information some how. Good to know.

Yes, the key is that last (4th) argument of the VLOOKUP function which allows for an approximate match. If it does not mind a perfect match, it will go to the first value it finds just below that value.