IF Function (Possibly) to Apply a Value Depending on Value

[Toria]

In column D, I have units. I have to apply values to the number of units. If
the number is between 0-50 units, I apply 0, if between 51-200, I would need
to apply .05, if between 201-500, i would need to apply .10 and so on. The
numbers go above 10,000. Is this possible? I'm terrible at nested IFs. I
tried and kept getting errors and didn't see any similar questions.

Thank you!

 

[Pete_UK]

Set up a 2-column table somewhere like this:

0 0
51 0.05
201 0.1
501 0.2

and so on for your other values. Suppose this occupies cells X1 to Y8.

Then you can use this formula:

=VLOOKUP(D1,X$1:Y$8,2)

to return the appropriate factor depending on the value in D1. Copy
down as required.

Hope this helps.

Pete

 

[JoeU2004]

In column D, I have units. I have to apply values to the number of units.
If
the number is between 0-50 units, I apply 0, if between 51-200, I would
need
to apply .05, if between 201-500, i would need to apply .10 and so on.
The
numbers go above 10,000. Is this possible? I'm terrible at nested IFs. I
tried and kept getting errors and didn't see any similar questions.

I don't know that "apply" means: add to something (what?), multiply,
divide, etc?

I will ignore that and simply demonstrate how to return the "applied" factor
(0, 0.05, 0.10, etc).

Also, I see no numerical progression of tiers, 0-50, 51-200, 201-500 etc. I
don't know what the next tiers would be, other than the next one starts with
501. Moreover, it is unclear what the next "applied" factor would be: 0.15
(adding 0.05 for each tier), or 0.20 (doubling for each tier), or something
else altogether.

Consequently, I would eschew any algebraic formulation of this. Instead, I
would suggest a lookup table in one form or another. That is the most
flexible method, albeit not always the most efficient. It is certainly
better than using nested IFs, especially for anything more than 8 tiers.

If the number of tiers is small (managable), you might consider a LOOKUP()
expression of the form LOOKUP(D1,{0,51,201,...},{0,0.05,0.10,...}), where
you fill in the "..." with the remaining numbers.

But if the number of tiers is large, you might consider a lookup table in an
out-of-the-way range of cells in the workbook. For example, if X1:X20
contains the tier lower bounds 0, 51, 201 etc, and Y1:Y20 contains the
corresponding "applied" factors 0, 0.05, 0.10 etc., you could use a LOOKUP()
expression of the form LOOKUP(D1,X1:Y20).

Hope that helps. If not, I suspect you need to provide more specifics.

For example, what are __all__ the tiers breakpoints (0, 51, 201 etc), and
what are __all__ the "applied" factors (0, 0.05, 0.10 etc); or what are the
rules for determining those breakpoints and corresponding factors? And are
the number of units in column D always integral values (whole numbers)?

Also, what does "apply" mean? What do you "apply" it to, and how? Provide
some numeric examples to demonstrate your expectations.

 

[Toria]

Hi Joe,

Sorry about the lack of info. "Apply" just meant to return that value, not
to perform any kind of calculation. I don't know the higher tiers either.
That's a question for my boss. Thank you so much for your reply. This is
awesome, and you're right, much better than many nested IFs. I'm going to
work on this in the morning.

 

[Toria]

Pete,

Brilliant yet I wish I would have thought of it. I don't know why I was
stuck on the IF route. Thank you so much!!

 

[Pete_UK]

You're welcome, Toria - thanks for feeding back.

Pete