asked on

EXCEL calculation matter

I have a long list of numbers, thousands of rows, where I need to divide the number in the row by 57. Problem is, some of those rows are empty and I need for them to stay empty. So it looks something like this: (these are us dollars by the way)

ROW AC
1 3.65
2 3.44
3 7.34
4
5 8.01
6
7 9.11
8 10.19

So i need to divide this entire col by a percentage - .57% so that the value increases to the new number. If I simply do the first one (=sum:AC1/.57) and drag it down the length of the col to copy the math, it works but puts 0.00 in the empty fields, which I do not want.

Is there another means by which I can accomplish this?

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jorge Paulino

You can do something like this:

If(AC1="","",AC1/.57)

helpfinder

select the cells where you formula is and right click to Format cell. Choose Custom format and use 0,00;-0;;@ - that leavs numbers as they are (with 2 decimal places) but zeros will be blank

Rob Henson

Using jpaulino's suggestion won't leave the blank rows as blank result, but will look empty. This might not be an issue other than for navigating the column. In the result column if you press End Down or Ctrl + Down the cursor will go to bottom of list, whereas my suggestion will leave cell empty and navigation will stop at the cell before the next blank.

Thanks
Rob H

BTW my formula should have been =AC1/0.57 but other comment had come in by the time I noticed the typo.

Steve

OK.. another way...

1) copy the number column
2) Paste to the next empty column
3) Highlight this new column (may already be highlighted after paste)
4) Ctrl+f (open find dialog)
5) Find * and Replace with .57
5.1) new column should now just have .57 where values were (and blanks still there)
6) copy the new column of blanks and .57s
7) select the old column of original values
8) Paste using Paste Special > settings to be > VALUES, DIVIDE , SKIP BLANKS.
9) Delete the old column now it is not needed.

This will change the values leaving no formula.

nutsch

With Steve's approach, you just need to type .57 in one cell, copy that cell, then jump into
7) select the old column of original values
8) Paste using Paste Special > settings to be > VALUES, DIVIDE , SKIP BLANKS.
9) delete the .57 cell

Thomas

Steve

Thomas, if you do that you will get zero where you have blanks... not OK.

Rob Henson

Combination of methods:

Apply Autofilter to column of values and hide blanks. Type 0.57 in first visible cell and drag down remainder of column. This will only populate visible cells.

Take off Autofilter and then continue with Steve's suggestion from Step 6.

Thanks
Rob H

nutsch

To avoid blanks,

1. Filter value column on non blanks
2. Type .57 in any available blank cell, copy that cell
3. Select the filtered values
4. Alt ; (Alt + semi-column) will select only the visible lines (excluding the blanks)
5. Paste using Paste Special > VALUES, DIVIDE
6. delete the .57 cell

Thomas

Rob Henson

Are there any genuine non blank zero entries?

If not:
Filter on non blank
Copy 1 cell of 0.57
Paste Special > Values > Divide
Refilter on zero
Delete visible.

Thanks
Rob H

Rob Henson

@Thomas - Didn't know about the Alt + ; keyboard trick for visible cells. Noted for future!!

nutsch

One of my favorite shortcuts. I actually often use for multiplication and division like requested by the asker, with one of the Multiply_Range or Divide_Range macros below.

Sub Multiply_Range()
Dim dblMultiplier As Double
'   This is required in case cancel is clicked.
On Error GoTo UserCancelled

'get multiplier
dblMultiplier = InputBox("Enter Multiplier", "Range Multiplication", 1000)

Multiply_Range_Function dblMultiplier
    
UserCancelled:

End Sub

Private Sub Multiply_Range_Function(dblMultiplier As Double)
'This macro multiplies all values in a range by the user-entered multiplier

Dim rngToMultiply As range
Dim intColCount As Long, intRowCount As Long
Dim strCalc As String

strCalc = application.Calculation
application.Calculation = xlCalculationManual

'   Assign selection to object variable
Set rngToMultiply = selection

'   Loop through source range multiplying all value-containing cells and _
painting the formula-containing cells blue
Dim cl As range
'   Loop through source range assigning any formulae found
'   to the equivalent cell of the destination range.
For Each cl In rngToMultiply.Cells
        If cl.HasFormula Then
            cl.Interior.ColorIndex = 5
        Else
            If Not cl = "" Then cl = cl.Value * dblMultiplier
        End If
Next

UserCancelled:

application.Calculation = strCalc

End Sub

Sub Divide_Range()
Dim rngToDivide As range
Dim dblDivider As Double
Dim strCalc As String
Dim intColCount As Long, intRowCount As Long

strCalc = application.Calculation
application.Calculation = xlCalculationManual

'   Assign selection to object variable
Set rngToDivide = selection

'   This is required in case cancel is clicked.
'   Type 8 input box returns a range object if OK is
'   clicked or False if cancel is clicked.  I do not
'   know of a way to test for both cases without
'   using error trapping
On Error GoTo UserCancelled

'   Assign object variable to user-selected cell

dblDivider = InputBox("Enter Divider", "Range Division", 1000)

If dblDivider = 0 Then
    MsgBox ("Cannot divide by zero, sorry!")
    Exit Sub
End If

Dim cl As range
'   Loop through source range assigning any formulae found
'   to the equivalent cell of the destination range.
For Each cl In rngToDivide.Cells
        If cl.HasFormula Then
            cl.Interior.ColorIndex = 5
        Else
            If Not cl = "" Then cl = cl.Value / dblDivider
        End If
Next
 
UserCancelled:

application.Calculation = strCalc

End Sub

Open in new window

nutsch

And by the way Rob, congrats on your new hard-won Genius status!

Rob Henson

Thanks Thomas, another t-shirt in the drawer!!