Understanding this If Statement formula

[Chris McGlothen]

I'm trying to determine exactly what this formula is doing. I know that if Cell "A2" equals the desired text it completes the rest and if it doesn't it returns 0. I also understand that if cell B2 isn't greater than 15 it returns back 15. I also understand that if cell B2 is between 16-35 it returns a value of 25. I also see that anything greater than 35 it returns back 35. I guess my bigger question, is there a way to rewrite this? TIA

Please Login or Register  to view this content.

[AliGW]

It doesn't really make sense - what is it suppoised to do?

[HansDouwe]

is there a way to rewrite this?

Yes, this formula does the same:

Please Login or Register  to view this content.

[Chris McGlothen]

In a nutshell it is used to determine the lot size for manufacturing purposes. It's looking at the number of pieces that can be built in "X' amount of hours and then it determines how to set the lot size to be built. It says if the qty is less than 15, build 15. If it's greater than 15 but not greater than 35 build 25 and if it's more than 35 then build lots of 35. I'm recreating a sheet and wanted to understand it a little more. I have never seen a formula like this before so I thought someone might have.

[Chris McGlothen]

It definitely works. Thanks for the formula. I definitely don't understand how this one works. I might just create a table and do an Index and Match to get the expected result.

[HansDouwe]

I might just create a table and do an Index and Match

You need INDEX and XMATCH with a search last to first otherwise the formula can not handle 15 and 35 correct.

In formula of Post #3, I have used XLOOKUP what is the same as INDEX and XMATCH. The tables are defined in the formula.

Here is another formula that does the job:

Please Login or Register  to view this content.

See attachment with all 3 formulas.

[Chris McGlothen]

Thank you HansDouwe. I appreciate you going above and beyond to help.

[HansDouwe]

You are Welcome

Thanks for the feedback and rep . Glad to have helped!

If there any questions left, don't hesistate to ask these questions here.