Looking for a non-IF formula to apply a specific formula based on a control cell contents

[Mr_Phil]

Hi everyone. I want to create a formula/method without making a big hairy IF statement.
I have equipment items coded by importance from 1 (Most) to 6 (Least). When ordering I have a specific formula in mind for each item code. I know I could create a six or more level IF statement as that is fairly straight forward. The workbook is under construction so I can add sheets, lookup tables, etc if needed. Right now every item has a minimum and maximum value assigned for possession/issue from warehouse purposes. So the Tier 1 items I total up all the "Max" numbers on the items and subtract out the on hand count. That tells me what I need to receive to issue those items to the techs and bring them all up to the max allowed per tech. The Tier 2 items are ordered up to the mid point (median) between the MIN & MAX. The Tier 3 Items are ordered to bring everyone up to the minimum allowance. Tier 4, 5, 6 all have unique circumstances. I'd like to have a single cell with the correct order qty based on what tier the part is on. Right now all I am seeing is a big ol IF statement. Anyone have an idea for a different approach. I am very open to learning new skills that lead to a solution so any insight is appreciated.

 

[awoohaw]

You can use the Choose or Switch Formulas. But, if you want a suggested formula you really need to help the forum help you. Please post a mini workbook using the xl2bb add in (Link below). You're asking for forum to decipher your prose into a workbook and thenn provide you with a solution If you can't install the add in, at least post a table (not a picture). And a scenario or two with expected results would be much appreciated as well.

 

[Mr_Phil]

You can use the Choose or Switch Formulas. But, if you want a suggested formula you really need to help the forum help you. Please post a mini workbook using the xl2bb add in (Link below). You're asking for forum to decipher your prose into a workbook and thenn provide you with a solution If you can't install the add in, at least post a table (not a picture). And a scenario or two with expected results would be much appreciated as well.

Hi. Thanks for commenting. I really didn't expect specifics. I was trying to frame it as a discussion rather than a "solve it" for me scenario. Basically I am trying to stretch my abilities by not doing what I am comfortable with. I can do IF and all the explicit lookups. I can write just enough VBA to know that I don't know much at all. I will try to come up with a mini workbook though.

 

[Mr_Phil]

You can use the Choose or Switch Formulas. But, if you want a suggested formula you really need to help the forum help you. Please post a mini workbook using the xl2bb add in (Link below). You're asking for forum to decipher your prose into a workbook and thenn provide you with a solution If you can't install the add in, at least post a table (not a picture). And a scenario or two with expected results would be much appreciated as well.

Hi again. I made a mini book. But xl2bb didn't install for me. So, the next best thing is the shared workbook on my google drive. Loading Google Sheets

Like I said above I am looking more for ideas or non-if formula approaches that might be more efficient or unique learning opportunity. Thank you though. I appreciate all feedback tremendously.

 

[awoohaw]

Well, I'll comply with your wish and not post a solution with your workbook until you've come back and said you have looked at Choose and Switch.
Choose function works like a lookup. the first argument needs to be a number, and it looks for that number argument from the rest of the ones lisgted in the function.
So, for instance Choose(4,1,2,3,4) says get the 4th item from this list, which is 4.
Switch works like Select Case in VBA, and IF statements. You give it formula or cell reference and then you give what "what if that value is x", then "y" in a list.
So for instance if Cell A2 contains "Blue": Switch(A2,"Red",1,"Green",2,"Blue",3) the result is 3.
if you need nested IF statements these can work, but are just as nested.

 

[awoohaw]

i've looked at your worksheet. My only question is do you need all 6 formulas in one cell? They seem pretty straightforward otherwise.

but, i'm going to sleep now. I'll look more into this in the morning.

 

[Mr_Phil]

i've looked at your worksheet. My only question is do you need all 6 formulas in one cell? They seem pretty straightforward otherwise.

but, i'm going to sleep now. I'll look more into this in the morning.

Sleep is always a great idea. The formulas can be anywhere as long as the result is fed into the order column. The first column and the order column will be copied and paste/values into the "official order form" we send to the vendor(s). I like the numbers and formulas side by side so I can follow the math. And, because ordering is as much a budget process as it is replenishment if the powers that be call me and want to know what/why about this/that all the numbers are there. And, setting a specific method for each tier ensures no "favortism" or "fell thru the cracks".

I just drove seven hours to my satellite warehouse. So, I am feeling the need for rest myself. I think a bit of light reading on choose and switch will help.

Thanks again.

 

[awoohaw]

I don't think you need choose and switch. But what i have is not one formula. This miniworkbook has one column of nothing but the formula for a specific tier.
Another column shows the tier that the choose and switch formulas need. Then combining all 6 formulas in a choose or switch function.

mini book.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	NOMENCLATURE	TIER	IN WAREHOUSE	IN THE FIELD	MINIMUM ALLOWANCE	MAXIMUM ALLOWANCE	MEDIAN ALLOWANCE	7 DAY USE	TIER	FORMULA	CHOOSE	SWITCH	TO ORDER	MANUAL INPUT
2	PART 1	1	25	12	36	56	46	5	1	19	19	19	TIER ONE ITEMS: MAXIMUM ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.	IF YOU PUT ANY NUMERIC VALUE IN THIS COLUMN IT WILL OVERRIDE WHATEVER THE TO ORDER COLUMN EVALUATED TO.
3	PART 2	2	35	15	61	84	73	11	2	23	23	23	TIER TWO ITEMS: MEDIAN ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
4	PART 3	3	55	63	86	112	99	17	3	-32	-32	-32	TIER THREE ITEMS: MINIMUM ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
5	PART 4	4	12	88	111	140	126	23	4	23	23	23	TIER FOUR ITEMS: JUST ORDER THE PRIOR 7 DAYS USAGE REGARDLESS OF ANY ON HAND COUNTS
6	PART 5	5	44	55	136	168	152	29	5	0	0	0	TIER FIVE IS MANUALLY COMPUTED AS THE VARIABLES ARE NOT QUANTIFIED YET SO THE ORDER COLUMN SHOULD REQUIRE MANUAL INPUT FROM COLUMN J.
7	PART 6	6	63	100	161	196	179	35	6	0	0	0	TIER SIX IS ZERO. FOR WHATEVER REASON WE ARE NO LONGER ORDERING THE ITEM.
8
Sheet1

Cell Formulas
Range	Formula
J2	J2	=IF(ISNUMBER(N2),N2,F2-D2-C2)
K2:K7	K2	=CHOOSE(I2, IF(ISNUMBER(N2),N2,F2-D2-C2), IF(ISNUMBER(N2),N2,G2-D2-C2), IF(ISNUMBER(N2),N2,E2-D2-C2), IF(ISNUMBER(N2),N2,H2), N2, IF(ISNUMBER(N2),N2,0))
L2:L7	L2	=SWITCH(I2, 1,IF(ISNUMBER(N2),N2,F2-D2-C2), 2,IF(ISNUMBER(N2),N2,G2-D2-C2), 3,IF(ISNUMBER(N2),N2,E2-D2-C2), 4,IF(ISNUMBER(N2),N2,H2), 5,N2, 6,IF(ISNUMBER(N2),N2,0))
J3	J3	=IF(ISNUMBER(N3),N3,G3-D3-C3)
J4	J4	=IF(ISNUMBER(N4),N4,E4-D4-C4)
J5	J5	=IF(ISNUMBER(N5),N5,H5)
J6	J6	=N6
J7	J7	=IF(ISNUMBER(N7),N7,0)
G2:G7	G2	=ROUND(MEDIAN(E2:F2),0)

 

[Mr_Phil]

I don't think you need choose and switch. But what i have is not one formula. This miniworkbook has one column of nothing but the formula for a specific tier.
Another column shows the tier that the choose and switch formulas need. Then combining all 6 formulas in a choose or switch function.

mini book.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	NOMENCLATURE	TIER	IN WAREHOUSE	IN THE FIELD	MINIMUM ALLOWANCE	MAXIMUM ALLOWANCE	MEDIAN ALLOWANCE	7 DAY USE	TIER	FORMULA	CHOOSE	SWITCH	TO ORDER	MANUAL INPUT
2	PART 1	1	25	12	36	56	46	5	1	19	19	19	TIER ONE ITEMS: MAXIMUM ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.	IF YOU PUT ANY NUMERIC VALUE IN THIS COLUMN IT WILL OVERRIDE WHATEVER THE TO ORDER COLUMN EVALUATED TO.
3	PART 2	2	35	15	61	84	73	11	2	23	23	23	TIER TWO ITEMS: MEDIAN ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
4	PART 3	3	55	63	86	112	99	17	3	-32	-32	-32	TIER THREE ITEMS: MINIMUM ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
5	PART 4	4	12	88	111	140	126	23	4	23	23	23	TIER FOUR ITEMS: JUST ORDER THE PRIOR 7 DAYS USAGE REGARDLESS OF ANY ON HAND COUNTS
6	PART 5	5	44	55	136	168	152	29	5	0	0	0	TIER FIVE IS MANUALLY COMPUTED AS THE VARIABLES ARE NOT QUANTIFIED YET SO THE ORDER COLUMN SHOULD REQUIRE MANUAL INPUT FROM COLUMN J.
7	PART 6	6	63	100	161	196	179	35	6	0	0	0	TIER SIX IS ZERO. FOR WHATEVER REASON WE ARE NO LONGER ORDERING THE ITEM.
8
Sheet1

Cell Formulas
Range	Formula
J2	J2	=IF(ISNUMBER(N2),N2,F2-D2-C2)
K2:K7	K2	=CHOOSE(I2, IF(ISNUMBER(N2),N2,F2-D2-C2), IF(ISNUMBER(N2),N2,G2-D2-C2), IF(ISNUMBER(N2),N2,E2-D2-C2), IF(ISNUMBER(N2),N2,H2), N2, IF(ISNUMBER(N2),N2,0))
L2:L7	L2	=SWITCH(I2, 1,IF(ISNUMBER(N2),N2,F2-D2-C2), 2,IF(ISNUMBER(N2),N2,G2-D2-C2), 3,IF(ISNUMBER(N2),N2,E2-D2-C2), 4,IF(ISNUMBER(N2),N2,H2), 5,N2, 6,IF(ISNUMBER(N2),N2,0))
J3	J3	=IF(ISNUMBER(N3),N3,G3-D3-C3)
J4	J4	=IF(ISNUMBER(N4),N4,E4-D4-C4)
J5	J5	=IF(ISNUMBER(N5),N5,H5)
J6	J6	=N6
J7	J7	=IF(ISNUMBER(N7),N7,0)
G2:G7	G2	=ROUND(MEDIAN(E2:F2),0)

I would not have thought of looking at it from an isnumber point of view. And I have not encountered choose and switch before either. I'm going to mark the thread as solved based on learning three different approaches from your comments. Thank you very much. This was really instructive and that was what I was going for.

One thing I did find on my own that I didn't know about was IFS. I learned a long time ago to nest the traditional if/then/else syntax in both excel and lotus. I like the IFS approach excepting it doesn't have an else or default value aspect. In my case where the formulas will produce a negative or a positive number I could wrap it up in a Max with the formula to the left and a ,0 to the right. That would convert the negative values to 0. But, IFS wins hands down if the nested if would be the alternative.

 

[awoohaw]

My pleasure. I am happy to have helped. I participate in the forum to learn new approaches as well as offering solutions when people post. It is great when you see multiple posts with different approaches to a solution. I've learned much here as well.

Regarding the ISNUMBER, when I was working on your question I was also considering using <>"" or ISBLANK(cellref) = FALSE, but those caused problems, so I had to use ISNUMBER since youll be inputting numbers.

Best wishes!