Formula to calculate differently based on original value
I have tried to use an "IF" formula to do this but to no avail... What I
want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
Formula to calculate differently based on original value
Try this for all 3 levels:
=A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6}) -- Biff Microsoft Excel MVP "Pam" wrote in message ... I have tried to use an "IF" formula to do this but to no avail... What I want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
Formula to calculate differently based on original value
Another way, which is more flexible, is to create a 2 column table like
this: ...........J..........K 1.......0........0.4 2.......1.8.....0.5 3.......2.4.....0.6 Then: =A1+LOOKUP(A1,J1:K3) Although you only have 3 levels and the original formula is not very long, using a table gives you the flexibility to easily update when the base values or the factor changes. You'd just update the table rather than having to manually update each formula. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this for all 3 levels: =A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6}) -- Biff Microsoft Excel MVP "Pam" wrote in message ... I have tried to use an "IF" formula to do this but to no avail... What I want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
Formula to calculate differently based on original value
"Pam" wrote:
if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. [....and] anything 2.4 or greater has 0.6 added. Perhaps: =A1 + 0.4 + 0.1*(A1=1.8) + 0.1*(A1=2.4) Caveat: When dealing with numbers with decimal fractions, things are not always what they appear. A cell that displays 1.8 might really have a value, for example, of 1.79. Consequently, instead of 2.3 as you might expect, you might see 2.2. So you might want to do, at least: =A1 + 0.4 + 0.1*(ROUND(A1,1)=1.8) + 0.1*(ROUND(A1,1)=2.4) Even better: round whatever formula is in A1, and round this formula, namely (assuming A1 is rounded): =ROUND(A1 + 0.4 + 0.1*(A1=1.8) + 0.1*(A1=2.4), 1) ----- original message ----- "Pam" wrote in message ... I have tried to use an "IF" formula to do this but to no avail... What I want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
Formula to calculate differently based on original value
Thanks- this worked perfectly!
"T. Valko" wrote: Try this for all 3 levels: =A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6}) -- Biff Microsoft Excel MVP "Pam" wrote in message ... I have tried to use an "IF" formula to do this but to no avail... What I want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
Formula to calculate differently based on original value
This also worked, but I could not drag down the formula (rec'd an error:
#N/A) because the formula was changing the J1:K3 values for each row (J2:K4, J3:K5, etc.). As long as I correct each formula, it does work. Any way to set it to change the first part (A1) as I drag while leaving the J/K part alone? "T. Valko" wrote: Another way, which is more flexible, is to create a 2 column table like this: ...........J..........K 1.......0........0.4 2.......1.8.....0.5 3.......2.4.....0.6 Then: =A1+LOOKUP(A1,J1:K3) Although you only have 3 levels and the original formula is not very long, using a table gives you the flexibility to easily update when the base values or the factor changes. You'd just update the table rather than having to manually update each formula. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this for all 3 levels: =A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6}) -- Biff Microsoft Excel MVP "Pam" wrote in message ... I have tried to use an "IF" formula to do this but to no avail... What I want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
Formula to calculate differently based on original value
If you're using a table make the references to the table absolute.
=A1+LOOKUP(A1,$J$1:$K$3) The $ signs will keep the references from changing when you copy the formula. -- Biff Microsoft Excel MVP "Pam" wrote in message ... This also worked, but I could not drag down the formula (rec'd an error: #N/A) because the formula was changing the J1:K3 values for each row (J2:K4, J3:K5, etc.). As long as I correct each formula, it does work. Any way to set it to change the first part (A1) as I drag while leaving the J/K part alone? "T. Valko" wrote: Another way, which is more flexible, is to create a 2 column table like this: ...........J..........K 1.......0........0.4 2.......1.8.....0.5 3.......2.4.....0.6 Then: =A1+LOOKUP(A1,J1:K3) Although you only have 3 levels and the original formula is not very long, using a table gives you the flexibility to easily update when the base values or the factor changes. You'd just update the table rather than having to manually update each formula. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this for all 3 levels: =A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6}) -- Biff Microsoft Excel MVP "Pam" wrote in message ... I have tried to use an "IF" formula to do this but to no avail... What I want to do is have a column of data that will be decimal values such as 0.6, 0.8, 1.8, 2.5, and so forth. Then, I want the formula in the next column to decide that if the value is less than 1.8, it should add 0.4 to it [0.6 becomes 1.0, 0.7 become 1.1, etc.] but if it is NOT less than 1.8, it should add 0.5 [1.8 becomes 2.3, 1.9 becomes 2.4, etc.]. If I can have two levels, that will suffice. However, it would be great if I could have a third level so that anything greater than or equal to 1.8 has 0.5 added to it, but anything 2.4 or greater has 0.6 added. Again, that third level is not absolutely necessary for my current purposes, but if it is possible, I'd like to be able to do that as well for future situations. (This may sound like an odd request, but it has to do with crediting people with time in workshops, and the more they attend, the more "bonus" time they accrue.) Thanks!! I use Excel 2003 at work and Excel 2007 at home. |
All times are GMT +1. The time now is 08:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com