Solved

EXCEL calculation matter

Posted on 2014-03-20
14
158 Views
Last Modified: 2014-03-25
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
Comment
Question by:mcpilot1
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 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

by:jpaulino
ID: 39942119
You can do something like this:

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

Expert Comment

by:helpfinder
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 31

Expert Comment

by:Rob Henson
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

by:Steve
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

by:nutsch
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

by:Steve
ID: 39942513
Thomas, if you do that you will get zero where you have blanks... not OK.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

Expert Comment

by:Rob Henson
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

by:nutsch
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 31

Expert Comment

by:Rob Henson
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 31

Expert Comment

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

Expert Comment

by:nutsch
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

Open in new window

0
 
LVL 39

Expert Comment

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

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now