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?
mcpilot1Asked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Apply a filter to your column of numbers and filter out the blanks.

Then apply the formula as suggested in the first row:

=A1/0.57

Then select the cells that need the formula, eg down the column, select as a block and Press Ctrl + D to fill down the column. This will only populate the visible ones.

You can then reset the filter to show all and copy the formula column and paste values onto the original column.

Thanks
Rob H
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You can do something like this:

If(AC1="","",AC1/.57)
0
 
helpfinderIT ConsultantCommented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Rob HensonFinance AnalystCommented:
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.
0
 
SteveCommented:
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.
0
 
nutschCommented:
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
0
 
SteveCommented:
Thomas, if you do that you will get zero where you have blanks... not OK.
0
 
Rob HensonFinance AnalystCommented:
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
0
 
nutschCommented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
@Thomas - Didn't know about the Alt + ; keyboard trick for visible cells. Noted for future!!
0
 
nutschCommented:
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

0
 
nutschCommented:
And by the way Rob, congrats on your new hard-won Genius status!
0
 
Rob HensonFinance AnalystCommented:
Thanks Thomas, another t-shirt in the drawer!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.