Conditional Formula Question

[Jeremy]

I need to set up a formula that allows the following:

79 or less = 0
80=80
81=81
82=82
100=100
101=102
102=104
103=106
104=108
105=110

Can anyone help?

Thanks

 

[HKaplan]

You mean I enter 101 but another cell auto displays 102? Or I enter
101 into a cell but upon hitting enter it displays 102 instead?

 

[Jeremy]

I have a formula that is set in the cell that will result in 101 however If
that happens i need it to say 102

 

[Fred Smith]

=if(a1<80,0,if(a1>100,(a1-100)*2+100,a1))

Regards,
Fred

 

[HKaplan]

A vlookup table would work. And use an if statement to first test for
<80. Like this. Lets say the formula in the cell is:
=sum(101+3), which equals 104, but you want 108:

=IF(SUM(101+3<80),0,VLOOKUP(SUM(101+3),mylookuptablerange,2,FALSE))

The mylookuptablerange might be a lookup table like this:
A B
80 80
81 81
82 82
100 100
101 102
102 104
103 106
104 108
105 110

Do you follow?

 

[HKaplan]

My lookup example assumed the numbers aren't always that consistant.
My example would permit changing any number to any other number.
Fred's response is much better if your table is that simple.