how can i set up multiple conditional formulas in excel?

[dillj]

I'm trying to set a value that depends on another value being between two
values. For example: IF A1 is between 0-50, then A2 is B2*C2. If A1 is
greater then 50, but less then 100 but less then 200, then A2 is B2*C3. If A1
is greater then 200 but less then 300, then A2is B2*C4. And so on.

 

[Pete_UK]

What is the range of the second criteria? 50-100 or 50-200? How many
criteria ranges will you have, and what are they? (Excel doesn't
understand "... and so on ...").

Pete

 

[dillj]

Pete,
Sorry about that. That's a typo. The 2nd range is 50-100, the third range is
100-200, etc. I know excel doesn't understand "and so on", I was just trying
to illustrate that I have multiple ranges, not just the ones I listed.

Thanks

 

[Pete_UK]

Yes, but how many ranges do you have? There is a limit of 7 nested
functions that you can have in Excel 2003 and earlier, so if you have
more ranges than 7 we can't use nested IFs - we have to do this in a
different way.

What are your ranges? Do they always go up in steps of 100, or does it
go something like 300-400, 400-500, then 500-750, 750-1000, then
1000-1500? What is the largest value that A1 can take? (And do you
have sufficient entries in column C?)

Pete

 

[dillj]

Each entry in C is related to the range. For example: for range 0-50, the C
value is 20%. for range 50-100, the C value is 18%. For now, the ranges all
go up at an even interval and the value in C goes down in an even interval. I
can use just 7, but what are the other ways if I need more then 7 nests in
the future?

I have about 2000 lines of data that all have different values that will
fall into the 7 ranges. I only have (for now) 7 ranges and 7 values to use in
relation to the range. I'm hoping that I can write one formula that I can
apply down the last column for all 2000 lines of data.

 

[Pete_UK]

Here's one way of doing it. If you have 20%, 18%, 16% etc in C1
downwards to C11 in -2% steps, then put the lower part of your ranges
in D1 downwards, i.e. 0, 50, 100, 200, 300, etc up to 900 in D11, then
put this formula in E1:

=INDEX(C$1:C$11,MATCH(A1,D$1$11,0))*B1

This will multiply B1 by the appropriate percentage depending on the
value in A1. Copy the formula down your 2000 rows.

Hope this helps.

Pete

 

[Dave]

Hi,
You could use VLOOKUP. This would allow any number of criteria ranges. You
would need to put this criteria table in an out-of-the-way place. I've chosen
Y1:Z9

Row Y Row Z
0 =$B$2*$C2
50 =$B$2*$C3
100 =$B$2*$C4
200 =$B$2*$C5
300 =$B$2*$C6
400 =$B$2*$C7
500 =$B$2*$C8
600 =$B$2*$C9
700 =$B$2*$C10


You can add to this table as necessary.

Then in A2:
=VLOOKUP($A$1,$Y$1:$Z$9,2)
Note: Values of A1 greater than the last criteria (in this case, 700) will
always return the last criteria (B2*C10)
Change the $Y$1:$Z$9 to match the position of your criteria table.

Regards - Dave.

 

[Dave]

Perhaps this wil make the criteria table clearer

Row Y Row Z
0 =$B$2*$C2
50 =$B$2*$C3
100 =$B$2*$C4
200 =$B$2*$C5
300 =$B$2*$C6
400 =$B$2*$C7
500 =$B$2*$C8
600 =$B$2*$C9
700 =$B$2*$C10

Dave.

 

[Dave]

Pooh!
How come this system removes all my spaces? I'll use dots instead.

Row Y............Row Z
0..................=$B$2*$C2
50................=$B$2*$C3
100..............=$B$2*$C4
200..............=$B$2*$C5
300..............=$B$2*$C6
400..............=$B$2*$C7
500..............=$B$2*$C8
600..............=$B$2*$C9
700..............=$B$2*$C10

 

[dillj]

thanks dave. I'll test yours. this is what I came up with
=IF(A1<50,B2*C2,IF(A1<100,B2*C3))
I had to add $ where necessary, but this worked pretty well.

thanks again.

 

[dillj]

thanks Pete. I was working on it and came up with anther formula that works.
i posted it to dave's idea. i'm gonna try yours too.

thanks everyone!

 

[Pete_UK]

Thanks for feeding back.

Your formula only tests for two values, but you could continue adding
more IFs for the other ranges up to a maximum of seven. My formula
(and Dave's) allows for more ranges, and you can change the values
without having to amend the formula.

Pete

 

[The Dean]

Hello sir,

It seems that you know what you're doing so I'll ask you. I'm trying to
create a formula to determine how much break time to deduct from employees
hours by using the amount of hours the employees have. For example, cell A2
has 10 hours for john smith. B2 is where I would like the formula. My ranges
are as follows: if A2 is greater than or equal 8, it'll result in 1.0, if A2
is less than or equal to 7.9 but greater than or equal to 4, it'll result in
..5, if A2 is less than or equal to 3.9, it will result in 0.

Thank you very much for your time

 

[Pete_UK]

Thanks for the vote of confidence !! <bg>

Try this formula in B2:

=IF(A2<4,0,IF(A2<8,0.5,1))

You can copy it down column B for as many entries as you have in
column A, though you might want to adjust the cell reference to your
real one first (if different).

Hope this helps.

Pete

 

[Harsh Bahal]

Hi ! I was on the look out for something like this. I have a similar problem.
In cell A1 I have sale target in value,in cell A2 i have achievement of sales
in value and in cell A3 I have % achievement. Now basis this from a grid (
say coloumn c iwth the range & coloumn D with the resulting absolute value)of
achievement <87% earnings 0, 87 to 90% earnings 1000 and so on ( say upto 10
slabs) I want to enter a formula in A4 which basis the % achievement (cell
A3), the resulting earnings will appear.
I have tried out Pete's & Dave's formulas, but it did not work out.
Pl help.
Thanks in anticipation.
Harsh

 

[Harsh Bahal]

Hi Dave,
Presume by "Row" you actually mean the coloumns