Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# EXCEL calculation matter

Posted on 2014-03-20
Medium Priority
166 Views
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?
0
Question by:mcpilot1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 4
• 2
• +2

LVL 33

Accepted Solution

Rob Henson earned 1500 total points
ID: 39942117
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

LVL 48

Expert Comment

ID: 39942119
You can do something like this:

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

LVL 19

Expert Comment

ID: 39942125
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

LVL 33

Expert Comment

ID: 39942131
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

LVL 24

Expert Comment

ID: 39942163
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

LVL 39

Expert Comment

ID: 39942357
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

LVL 24

Expert Comment

ID: 39942513
Thomas, if you do that you will get zero where you have blanks... not OK.
0

LVL 33

Expert Comment

ID: 39942717
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

LVL 39

Expert Comment

ID: 39942728
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

LVL 33

Expert Comment

ID: 39942750
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

LVL 33

Expert Comment

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

LVL 39

Expert Comment

ID: 39942800
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
``````
0

LVL 39

Expert Comment

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

LVL 33

Expert Comment

ID: 39943985
Thanks Thomas, another t-shirt in the drawer!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦