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

Mr_Phil

Board Regular
Joined
May 28, 2018
Messages
141
Office Version
  1. 365
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLMN
1NOMENCLATURETIERIN WAREHOUSEIN THE FIELDMINIMUM ALLOWANCEMAXIMUM ALLOWANCEMEDIAN ALLOWANCE7 DAY USETIERFORMULACHOOSESWITCHTO ORDERMANUAL INPUT
2PART 11251236564651191919TIER 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.
3PART 223515618473112232323TIER TWO ITEMS: MEDIAN ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
4PART 3355638611299173-32-32-32TIER THREE ITEMS: MINIMUM ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
5PART 441288111140126234232323TIER FOUR ITEMS: JUST ORDER THE PRIOR 7 DAYS USAGE REGARDLESS OF ANY ON HAND COUNTS
6PART 554455136168152295000TIER FIVE IS MANUALLY COMPUTED AS THE VARIABLES ARE NOT QUANTIFIED YET SO THE ORDER COLUMN SHOULD REQUIRE MANUAL INPUT FROM COLUMN J.
7PART 6663100161196179356000TIER SIX IS ZERO. FOR WHATEVER REASON WE ARE NO LONGER ORDERING THE ITEM.
8
Sheet1
Cell Formulas
RangeFormula
J2J2=IF(ISNUMBER(N2),N2,F2-D2-C2)
K2:K7K2=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:L7L2=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))
J3J3=IF(ISNUMBER(N3),N3,G3-D3-C3)
J4J4=IF(ISNUMBER(N4),N4,E4-D4-C4)
J5J5=IF(ISNUMBER(N5),N5,H5)
J6J6=N6
J7J7=IF(ISNUMBER(N7),N7,0)
G2:G7G2=ROUND(MEDIAN(E2:F2),0)
 
Upvote 0
Solution
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
ABCDEFGHIJKLMN
1NOMENCLATURETIERIN WAREHOUSEIN THE FIELDMINIMUM ALLOWANCEMAXIMUM ALLOWANCEMEDIAN ALLOWANCE7 DAY USETIERFORMULACHOOSESWITCHTO ORDERMANUAL INPUT
2PART 11251236564651191919TIER 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.
3PART 223515618473112232323TIER TWO ITEMS: MEDIAN ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
4PART 3355638611299173-32-32-32TIER THREE ITEMS: MINIMUM ALLOWANCE MINUS WHAT IS IN THE FIELD TO DETERMINE NEED. THEN SUBTRACT WAREHOUSE TO SEE HOW MANY TO ORDER.
5PART 441288111140126234232323TIER FOUR ITEMS: JUST ORDER THE PRIOR 7 DAYS USAGE REGARDLESS OF ANY ON HAND COUNTS
6PART 554455136168152295000TIER FIVE IS MANUALLY COMPUTED AS THE VARIABLES ARE NOT QUANTIFIED YET SO THE ORDER COLUMN SHOULD REQUIRE MANUAL INPUT FROM COLUMN J.
7PART 6663100161196179356000TIER SIX IS ZERO. FOR WHATEVER REASON WE ARE NO LONGER ORDERING THE ITEM.
8
Sheet1
Cell Formulas
RangeFormula
J2J2=IF(ISNUMBER(N2),N2,F2-D2-C2)
K2:K7K2=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:L7L2=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))
J3J3=IF(ISNUMBER(N3),N3,G3-D3-C3)
J4J4=IF(ISNUMBER(N4),N4,E4-D4-C4)
J5J5=IF(ISNUMBER(N5),N5,H5)
J6J6=N6
J7J7=IF(ISNUMBER(N7),N7,0)
G2:G7G2=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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top