Formula

[Usman]

Dear Fellows,

I have been working on a sheet and have encountered a problem.
I have around 1000 rows with values ranging from 2.693466 to 12.256987

I want if the value is under 2.8 replace that value with the previous allowed value and if the value exceed 9.5 replace the values with the previously value.


as an example. the formula should replace the value 2.693466 by 2.899442
2.900890 and also 2.680346 by 2.899442
2.899442 and likewise if value exceed 9.500000 replace it
2.693466 the with the previous value.
2.680346 =if(or( B2>9.5;B2<2.8);B1;B2)
9.346484 i hav made this formula but its not working when
9.324685 values exceed the limits.
9.492468
9.504648
.
.
.
.
.
. Thanks

 

[Somendra Misra]

Hi Usman,

Consider your values in column A1:A1000. Put this formula in B1 and copy down.

=IF(A1<2.8,2.8,IF(A1>9.5,9.5,A1))

Just advise if any issue.

Regards!

 

[Usman]

Thanks Somendra Misra it worked perfect. (y)

 

[Somendra Misra]

@Usman

Glad you slove it.

Regards!

 

[Usman]

Dear Misra,

I want to make it more better. I want the value which exceeds the limit 9.5 to be replaced by the previous value. In the following example i want to replace the value 9.504648 by 9.492468 not by 9.5.
For example
9.346484
9.324685
9.492468
9.504648

 

[bobhc]

Good day Usman

Somendra has done all the work all you have to do is modify his formula

=IF(A1<2.8,2.8,IF(A1>9.492468,9.492468,A1))


A more discriptive title may have attracted more help,"Formula" says nothing about the problem


.

 

[Usman]

Dear bobhc,

you are not getting my point. I am interested to make a formula which will replace the current value by the precious value(if it is exceeding limit 9.5 or if the value falls below 2.8) . I have 1000 rows and i cannot simply put the value 9.492468 because then each time the value exceeds it will replace that value by 9.492468 and may be in some other case that value is 9.499980.

 

[Somendra Misra]

Dear Usman,

I think what bobhc suggested is good, but what you are asking for is an automatic process in which after you enter a value in a cell it will check for the range and replace it automatically wit value of your choice. If this you want than I think it can be done through VBA and not formula.

Suggest is this what you want?

Regards!