Multiple Condition with Multiple Results

[dunerak]

Can someone please teach me how I would format a cell to do the following?:

If A1 is 16-37, then B1 is .25
If A1 is 38-52, then B1 is .50
If A1 is 53-59, then B1 is .75
If A1 is 60-75, then B1 is 1.00
If A1 is 76-97, then B1 is 1.25
If A1 is 98-112, then B1 is 1.50
If A1 is 113-119, then B1 is 1.75
If A1 is 120-157, then B1 is 2.00, etc.

There would actually be 16 conditions so that the result would go up to 4.00.
Ideally, the result would actually print in A1, but I don't think that's possible.


Thanks in advance for anyone willing to respond.

[FDibbins]

Hi, welcome to the forum

I would set this up in a small table (below), then use a VLOOKUP...

	A	B	C	D
2	16	0.25		60
3	38	0.50		1
4	53	0.75
5	60	1.00
6	76	1.25
7	98	1.50
8	113	1.75
9	120	2.00

D2= your value
D3=VLOOKUP(D2,$A$2:$B$9,2,1)

When you add your extra variables, just increace the range (bolded)

[Ron Coderre]

With this table in D1:E9
Data Range

	D	E
1	Floor	Value
2	16	0.25
3	38	0.50
4	53	0.75
5	60	1.00
6	76	1.25
7	98	1.50
8	113	1.75
9	120	2.00

These formulas both return the values you're looking for.
Assume each increment is 0.25

Please Login or Register  to view this content.

Is that something you can work with?

[Aardigspook]

Hi, welcome to the forum.

If you really, really want to work with just two cells, it is possible to do this with a nested IF statement, starting with your largest number, like this in B1:

Please Login or Register  to view this content.

(I can't see a pattern in your A1 numbers, so I can't add the >X figures to go up further - put them at the start of the formula, with another closing bracket at the end for each IF you add.)

However, I strongly suggest you go with one of the solutions above instead.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[Gunther Maplethorpe]

An alternative strategy may work if you have a later version of Excel. Try using the following nested IF set in cell B1:

Please Login or Register  to view this content.

EDIT: Looks like Aardigspook beat me to it! Note that we took slightly different versions of the same idea. Also note that depending on the version of Excel you have, you might be limited to only 8 IF statements (later versions will allow up to 64).

[dunerak]

Thanks! You guys rock. I'll try out your suggestions on a test spreadsheet. I am limited to 2 cells, so it looks like I'm going with the IF function. I sure do appreciate your fast responses.

[Ron Coderre]

Do NOT go with the multiple nested IF formula.

If you must use only the two cells, consider this:

Please Login or Register  to view this content.

Or...if the increments are always 0.25

Please Login or Register  to view this content.

The extra zeros are just placeholders for the other values that make up the list of 16.

[dunerak]

Hi Ron. I don't doubt you, but may I ask why so I can learn?

[dunerak]

Wanted to let you know I used the first IFERROR formula and it worked like a charm. Exactly what I needed. Thanks a million times over.

[FDibbins]

nested IF's can get very confusing to debug, and the longer/more involved they get, the harder it becomes to add to them.

Is there a compelling reason why you cannot have a small table (which you can hide if you want)

[FDibbins]

nested IF's can get very confusing to debug, and the longer/more involved they get, the harder it becomes to add to them.

Is there a compelling reason why you cannot have a small table (which you can hide if you want)

[Aardigspook]

Do NOT go with the multiple nested IF formula.

may I ask why so I can learn?

nested IF's can get very confusing to debug, and the longer/more involved they get, the harder it becomes to add to them.

I always forget about using arrays - the bits between the curly brackets {} - in formulae. Definitely go with Ron's formula not mine - it's a LOT better for the reasons given above.

@Ron - thanks for the reminder, enjoy the rep.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[dunerak]

FD, I didn't know the table could be embedded, and since my familiarity with Excel is limited, it looked overwhelming. It is not my spreadsheet and I wanted to keep it as simple as possible so my associate would be willing to try it. Believe it or not, Ron's formula made sense to me in how it was constructed. And I am happy to report it is now being used in the spreadsheet and we are all quite happy with it. Thanks to everyone who shared their knowledge with me. Such a nice thing to do.

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)