Formula to calculate differently based on original value

[Pam]

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.

 

[T. Valko]

Try this for all 3 levels:

=A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6})

 

[T. Valko]

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

Try this for all 3 levels:

=A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6})

--
Biff
Microsoft Excel MVP

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.

 

[JoeU2004]

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 -----

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.

 

[Pam]

Thanks- this worked perfectly!

Try this for all 3 levels:

=A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6})

--
Biff
Microsoft Excel MVP

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.

 

[Pam]

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?

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

Try this for all 3 levels:

=A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6})

--
Biff
Microsoft Excel MVP

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.

 

[T. Valko]

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

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?

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

Try this for all 3 levels:

=A1+LOOKUP(A1,{0;1.8;2.4},{0.4;0.5;0.6})

--
Biff
Microsoft Excel MVP


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.