the value should not be more than 0.8

[srpavar]

i have a date in colum a like this
1.8
3.8
0.0
3.5
1.1
0.8
5.6
4.8
4.5
0.0
0.0
i want to write a formual in column B, if the value is greater than 0.8 then
the value should be less than 0.8, in this manner. at any point it should not
go above 0.8
0.8
0.6
0
0.7
0.6
0.8
0.8
0.5
0.4
0
0
can anyone help me in this, i would be very grateful, thanks in advance.

 

[Mike H]

Hi,

1.8 > 0.8
3.8 > 0.6

I can understand why 1.8 becomes 0.8 but why does 3.8 get converted to 0.6?

Mike

 

[sajay]

use this =max(a1,0.8) and copy down

 

[Mike H]

You may have meant MIN but this still doesn't give the values the OP wanted

 

[Sandy Mann]

What is the logic in 1.8 becoming 0.8, 3.8 becoming 0.6, 3.5 becoming 0.7
etc?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk

 

[sajay]

oops
min(cell, .80) should produce the results

You may have meant MIN but this still doesn't give the values the OP
wanted

 

[Pete_UK]

Another approach might be to use this:

=MOD(A1,0.8)

and copy down, but this does not always produce the numbers in your
example:

1.8 0.2
3.8 0.6
0.0 0.0
3.5 0.3
1.1 0.3
0.8 0.0
5.6 0.0
4.8 0.0
4.5 0.5
0.0 0.0
0.0 0.0

Hope this helps.

Pete