I have a big range of data containing numeric values. but due to newer requirement to the maximum and minimum value the numeric data, I have to modified a lot of cells setting the value of each cell to its current value except if it is out of range, in that case I should just take the minimum value or the maximum and store it in the same cell.
Note that I don't want to store the formula in the excel file, just apply it to a range of values once and store in the cell the result value not the formula.
I'll try to make it clearer. Suppose I have at row 1 the values in columns A, B and C:
20 42 53
I want to select the range A1:C1, and apply to each the formula:
= if($cell > 50; 50; if($cell < 25;25; $cell))
I used $cell to denote the current cell where the calculation takes place, this doesn't work, is it just to illustrate what I'm trying to do.
As a result I expect:
25 42 50
But once again, just the values and not the formula. Also note that I can't not write the formula above in cell A1 for that cell because it is a circular reference, which raises an error.
A possible approach would be to have two sheets one the current values and the second with the formula with the values in the correct range, but this isn't possible because the file is processed by a program which does not expect formulas neither expects multiple sheets.
Hope I made my point clear.