Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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 On Apr 24, 2:24*pm, wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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 On Apr 24, 3:02*pm, wrote: 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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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" wrote: 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 On Apr 24, 3:02 pm, wrote: 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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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:D$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 On Apr 24, 3:41*pm, wrote: 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" wrote: 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 On Apr 24, 3:02 pm, wrote: 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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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. "Dave" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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" wrote: 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:D$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 On Apr 24, 3:41 pm, wrote: 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" wrote: 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 On Apr 24, 3:02 pm, wrote: 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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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 On Apr 24, 7:45*pm, wrote: 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" wrote: 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:D$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 On Apr 24, 3:41 pm, wrote: 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" wrote: 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 On Apr 24, 3:02 pm, wrote: 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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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 On Jun 26, 10:40*pm, The Dean <The wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
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 "Pete_UK" wrote: 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:D$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 On Apr 24, 3:41 pm, wrote: 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" wrote: 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 On Apr 24, 3:02 pm, wrote: 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" wrote: 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 On Apr 24, 2:24 pm, wrote: 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can i set up multiple conditional formulas in excel?
Hi Dave,
Presume by "Row" you actually mean the coloumns "Dave" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formulas with multiple possibilites | Excel Discussion (Misc queries) | |||
How do I apply conditional formulas across multiple columns? | Excel Worksheet Functions | |||
Conditional formulas between multiple worksheets | Excel Worksheet Functions | |||
How to create Multiple Conditional Formulas in a single cell? | Excel Discussion (Misc queries) | |||
conditional formulas in excel | Excel Worksheet Functions |