1

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.

1
  • Ok i found a solution using vba script, i will post it tomorrow (currently i can't due to the 8 hours limit)
    – Javier Mr
    May 7, 2013 at 16:26

3 Answers 3

1

Cells in Excel contain either formulas or values. You can use the current cell in a formula but it is ignored for computation.

As you mention you could have the values on one page and the formulas on another. I'm not sure how this other program is processing the Excel file but if you save it to something like a CSV only the values will be exported (and you specify which sheet is exported).

The only other option I can think of would be to create a new row/column using a formula to modify the values. Then use Copy/Paste Special to paste back only the modified values in the original cells.

1

I'm guessing you want to superimpose a formula over a range of cells that already contain data. If that's the case, I think the only way you can accomplish this is with VBA that selects the range(s) in question and applies a formula when those ranges are filled. You could also hide rows if you want to use straight formulas, but that's probably not ideal.

1
  • I did came to the same conclusion as yours, it isn't quite as simple as entering a formula (implies programming) but it gets the job done
    – Javier Mr
    May 8, 2013 at 6:07
0

In fact the solution is quite simple and just hit me a few minutes ago. I could achieve my goal writing a simple vba macro wich just apply the desired ranges over the selected cells. The code:

Function applyMakeInRange(minVal As Double, maxVal As Double, cells As Range)

Dim cellVal As Double

For Each cell In cells.cells
    cellVal = cell.Value

    If cellVal > maxVal Then
        cellVal = maxVal
    Else
        If cellVal < minVal Then
            cellVal = minVal
        End If
    End If

    cell.Value = cellVal

Next

End Function

Sub makeInRange()

Dim maxVal As Double
Dim minVal As Double
Dim cells As Range

Set cells = Selection

maxVal = InputBox("Max Value")
minVal = InputBox("Min Value")

res = applyMakeInRange(minVal, maxVal, cells)

End Sub

Simply by saving the file as without macros (once it has been executed) should do the trick

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .