Entering data as a number OR percentage

J

jacob farino

I have two pressing questions, I'll post two seperate emails; thank-you, in
advance, for any insight you might provide!!

I have a spreadsheet that splits revenue into front-end and back-end fees.
Usually, we charge fees as a percent. In order to tally up and correctly
give me a total revenue and then use that number to give me an average
revenue, etc., I manually mutliply the sale by our fee percentage, and enter
than number into, say column D. (So: $10,000 sale listed in Column B, we
charged 3% fees, I would use a calculator [maybe not in this case!] to get
the number $300, and enter that into column D.) After I input that fee
amount, I have a formula that tabulates the total percent fee-charged in the
next column, Column E (in my example, this would then read 3%). Well, since
I get my data in both numeric form ($300) AND percent form (3%) [depends on
the source], can I create a formula that I can input EITHER into Column D OR
E and it cross fills the corresponding number?

So, in the above example, can I enter "3" into Column E and Column D would
automatically display $3000 and vice versa?

Jacob
 
J

jacob farino

Thanks for the quick response. That isn't exactly what I need, but close.
(I've never seen that excelforum spreadsheet before though, that is very
helpful!!)

Using your post, let me clarify. I enter $10000 in B3. How can I get the
formula down to just two boxes. If I put $1000 in D3, it auto-fills D4 to
1%. OR if I put 1% in D4, it auto-fills D3 to $1000.

This may not be possible, because i am attempting to display the results in
the formulae cells, but I'm sure there is a way to effectively accomplish
the same thing. (I have numerous columns on this spread sheet, so adding 4
columns per fee calculation will go far off the printable legal-size,
landscape page!)
 
M

martialtiger

I think I get what you're saying. Let me re-work the file. I'll pos
once it's completed. ;
 
R

Ron Rosenfeld

I have two pressing questions, I'll post two seperate emails; thank-you, in
advance, for any insight you might provide!!

I have a spreadsheet that splits revenue into front-end and back-end fees.
Usually, we charge fees as a percent. In order to tally up and correctly
give me a total revenue and then use that number to give me an average
revenue, etc., I manually mutliply the sale by our fee percentage, and enter
than number into, say column D. (So: $10,000 sale listed in Column B, we
charged 3% fees, I would use a calculator [maybe not in this case!] to get
the number $300, and enter that into column D.) After I input that fee
amount, I have a formula that tabulates the total percent fee-charged in the
next column, Column E (in my example, this would then read 3%). Well, since
I get my data in both numeric form ($300) AND percent form (3%) [depends on
the source], can I create a formula that I can input EITHER into Column D OR
E and it cross fills the corresponding number?

So, in the above example, can I enter "3" into Column E and Column D would
automatically display $3000 and vice versa?

Jacob
Click to expand...

I don't believe you can do it exactly as you describe without using VBA. One
way is to use a VBA event triggered macro. The code below should be fairly
well self-documented. It does make some assumptions which may or may not fit
in with what you need.

It allows you to change any one of the amounts Sales, Commission Dollars or
Commission Percent. However, if you change Sales, it assumes the Commission
Dollars are correct and calculates a new percentage.

You should format the columns as you want. The routine does not do any cell
formatting, although it could.

See if this helps.

To enter it, on your worksheet, right click the worksheet tab and select View
Code. Paste the code below into the window that opens.

====================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range
Dim CommissionPercent As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [B3:B1000]
Set CommissionDollar = [D3:D1000]
Set CommissionPercent = [E3:E1000]

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler
If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) / Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex, 1)
End If

End If

Application.EnableEvents = True

Exit Sub

ErrorHandler:
'Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
'MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

MsgBox ("Data Entry Error")

Application.EnableEvents = True

End Sub
===================================
--ron
 
J

jacob farino

Ron:

Thanks for the reply. The below VBA does exactly what I want. Here is my
quandry. since I split up my fees into two sections, I need the below VBA to
work on two sets of columns. Both reference Set Sales in B, but the other
set of fee/percent columns are K & L.
How do I duplicate or add the extra VBA function?

Thanks again.


Ron Rosenfeld said:
I have two pressing questions, I'll post two seperate emails; thank-you, in
advance, for any insight you might provide!!

I have a spreadsheet that splits revenue into front-end and back-end fees.
Usually, we charge fees as a percent. In order to tally up and correctly
give me a total revenue and then use that number to give me an average
revenue, etc., I manually mutliply the sale by our fee percentage, and enter
than number into, say column D. (So: $10,000 sale listed in Column B, we
charged 3% fees, I would use a calculator [maybe not in this case!] to get
the number $300, and enter that into column D.) After I input that fee
amount, I have a formula that tabulates the total percent fee-charged in the
next column, Column E (in my example, this would then read 3%). Well, since
I get my data in both numeric form ($300) AND percent form (3%) [depends on
the source], can I create a formula that I can input EITHER into Column D OR
E and it cross fills the corresponding number?

So, in the above example, can I enter "3" into Column E and Column D would
automatically display $3000 and vice versa?

Jacob
Click to expand...

I don't believe you can do it exactly as you describe without using VBA. One
way is to use a VBA event triggered macro. The code below should be fairly
well self-documented. It does make some assumptions which may or may not fit
in with what you need.

It allows you to change any one of the amounts Sales, Commission Dollars or
Commission Percent. However, if you change Sales, it assumes the Commission
Dollars are correct and calculates a new percentage.

You should format the columns as you want. The routine does not do any cell
formatting, although it could.

See if this helps.

To enter it, on your worksheet, right click the worksheet tab and select View
Code. Paste the code below into the window that opens.

====================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range
Dim CommissionPercent As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [B3:B1000]
Set CommissionDollar = [D3:D1000]
Set CommissionPercent = [E3:E1000]

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler
If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) / Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex, 1)
End If

End If

Application.EnableEvents = True

Exit Sub

ErrorHandler:
'Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
'MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

MsgBox ("Data Entry Error")

Application.EnableEvents = True

End Sub
===================================
--ron
Click to expand...
 
R

Ron Rosenfeld

Thanks for the reply. The below VBA does exactly what I want. Here is my
quandry. since I split up my fees into two sections, I need the below VBA to
work on two sets of columns. Both reference Set Sales in B, but the other
set of fee/percent columns are K & L.
How do I duplicate or add the extra VBA function?

Thanks again.
Click to expand...

You're welcome.

You could add just add the extra ranges to test in the macro.

Again, you have to decide what you want to have happen is Sales is changed. I
again left a priority for the Dollar entry, and also changed the percentages in
both of the ranges.

Something like this:

============================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range, CommissionDollar2 As Range
Dim CommissionPercent As Range, CommissionPercent2 As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [B3:B1000]
Set CommissionDollar = [D3:D1000]
Set CommissionPercent = [E3:E1000]
Set CommissionDollar2 = [K3:K1000]
Set CommissionPercent2 = [L3:L1000]


If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler

If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionDollar2) Is Nothing Then
CommissionPercent2(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent2) Is Nothing Then
CommissionDollar2(RowIndex, 1) = Target * Sales(RowIndex, 1)

ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) / Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex, 1)
End If

If Target.Value = 0 Then
CommissionPercent2(RowIndex, 1).ClearContents
CommissionDollar2(RowIndex, 1).ClearContents
ElseIf CommissionDollar2(RowIndex, 1) <> 0 Then
CommissionPercent2(RowIndex, 1) = CommissionDollar2(RowIndex, 1) / Target
ElseIf CommissionPercent2(RowIndex, 1) <> 0 Then
CommissionDollar2(RowIndex, 1) = Target * CommissionPercent2(RowIndex, 1)
End If

End If

Application.EnableEvents = True

Exit Sub

ErrorHandler:
MsgBox ("Data Entry Error")
Application.EnableEvents = True

End Sub
==============================

--ron
 
J

Jacob

I get the data entry error now, i had to change some of the columns, but I
updated just the Column letters for the first set of rules to match below,
but I still get the data entry error.
A
H I J K L
TOTALS 7 $1,069,000.00 $29,905.00 $16,208.50 $46,113.50
4.31%
AVERAGES 1.4 $152,714.29 $4,272.14 $2,315.50 $6,587.64
4.31%
SUBTOTAL 7 $1,069,000.00 $29,905.00 $16,208.50
$46,113.50 4.31%

Final Approval Date
Loan Officer Borrower's Name State Investor Title Company Month Day
Loan Size Front End Fees F.E. Fee % YSP YSP % Total Fees Total Fee %
Clifton Holland Joseph Lare MD Interfirst MIS $163,200.00
$2,000.00 5.00% $1,632.00 1.00% $3,632.05 2.23%
Clifton Holland Misty Budzinksy MD First Franklin MIS $116,000.00
$1,160.00 1.00% $1,160.00 1.00% $2,320.01 2.00%
Tim Dawson Lee Lowney NH Indymac Swafford & Hayes $348,000.00
$10,440.00 3.00% $5,650.00 1.62% $16,090.03 4.62%
Chris Chambers Sharron Proper KS New Century MIS $85,800.00
$5,000.00 5.83% $1,501.50 1.75% $6,501.56 7.58%
Paul Hooton Lucy Ahumada CA Indymac MIS $135,000.00 $3,825.00
2.83% $3,375.00 2.50% $7,200.03 5.33%
Paul Hooton Melinda McClenic PA First Franklin MIS $68,000.00
$1,360.00 2.00% $1,360.00 2.00% $2,720.02 4.00%
Jenee Burke Robert Donlon GA Indymac Swafford & Hayes $153,000.00
$6,120.00 4.00% $1,530.00 1.00% $7,650.04 5.00%



Ron Rosenfeld said:
Thanks for the reply. The below VBA does exactly what I want. Here is my
quandry. since I split up my fees into two sections, I need the below VBA to
work on two sets of columns. Both reference Set Sales in B, but the other
set of fee/percent columns are K & L.
How do I duplicate or add the extra VBA function?

Thanks again.
Click to expand...

You're welcome.

You could add just add the extra ranges to test in the macro.

Again, you have to decide what you want to have happen is Sales is changed. I
again left a priority for the Dollar entry, and also changed the percentages in
both of the ranges.

Something like this:

============================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range, CommissionDollar2 As Range
Dim CommissionPercent As Range, CommissionPercent2 As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [B3:B1000]
Set CommissionDollar = [D3:D1000]
Set CommissionPercent = [E3:E1000]
Set CommissionDollar2 = [K3:K1000]
Set CommissionPercent2 = [L3:L1000]


If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler

If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionDollar2) Is Nothing Then
CommissionPercent2(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent2) Is Nothing Then
CommissionDollar2(RowIndex, 1) = Target * Sales(RowIndex, 1)

ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) / Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex, 1)
End If

If Target.Value = 0 Then
CommissionPercent2(RowIndex, 1).ClearContents
CommissionDollar2(RowIndex, 1).ClearContents
ElseIf CommissionDollar2(RowIndex, 1) <> 0 Then
CommissionPercent2(RowIndex, 1) = CommissionDollar2(RowIndex, 1) / Target
ElseIf CommissionPercent2(RowIndex, 1) <> 0 Then
CommissionDollar2(RowIndex, 1) = Target *
Click to expand...
CommissionPercent2(RowIndex, 1)
 
R

Ron Rosenfeld

I get the data entry error now, i had to change some of the columns, but I
updated just the Column letters for the first set of rules to match below,
but I still get the data entry error.
Click to expand...

Jacob,

Most likely the reason for the error is that your data does not match the
assumptions I've made, even with the changes you've made regarding the Column
letters.

And also, the way the routine is written, *ANY* error would give that Data
Entry Error.

And, because of the line wrapping, I can't really decipher what you've posted
effectively.

If you send me a copy of your workbook, I'm sure I can figure it out. You can
use the email address xyz at rosenfeldonline.com -- replacing the ' at '
with the obvious.


--ron
 
R

Ron Rosenfeld

Jacob,

After reviewing your worksheet, I'm not sure why you were getting the errors.
The worksheet you sent did not have the modified event macro to include the
extra columns and I did not get any errors with the macro that was included in
that.

I modified it to include the two extra columns and it seems to work OK. The
modified routine is below and seems to work OK on the worksheet you sent me.

In addition, I the formula for Total Fees may be incorrectly including the
percent FE fee in the total. Your formula is =SUM(I7:K7). Should it not be
=I7+K7? If you add in J7, you'll also be adding in the percent FE fees.

I would also suggest a Conditional Format to make the DIV/0 errors invisible.

Well, see if this works. If it gives errors, send back a copy of the worksheet
that gives the errors.

===========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range
Dim CommissionPercent As Range
Dim YSPDollar As Range
Dim YSPpercent As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [H7:H65000]
Set CommissionDollar = [I7:I65000]
Set CommissionPercent = [J7:J65000]
Set YSPDollar = [K7:K65000]
Set YSPpercent = [L7:L65000]

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler
If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, YSPDollar) Is Nothing Then
YSPpercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, YSPpercent) Is Nothing Then
YSPDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) / Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex, 1)
End If

If Target.Value = 0 Then
YSPpercent(RowIndex, 1).ClearContents
YSPDollar(RowIndex, 1).ClearContents
ElseIf YSPDollar(RowIndex, 1) <> 0 Then
YSPpercent(RowIndex, 1) = YSPDollar(RowIndex, 1) / Target
ElseIf YSPpercent(RowIndex, 1) <> 0 Then
YSPDollar(RowIndex, 1) = Target * YSPpercent(RowIndex, 1)
End If

End If

Application.EnableEvents = True

Exit Sub

ErrorHandler:
'Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
'MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

MsgBox ("Data Entry Error")

Application.EnableEvents = True

End Sub
============================================

--ron
 
J

jacob farino

Ron:

I don't know why I received that error message earlier, but i recopied the
below code and it works perfectly. That is awesome, thank-you.

As far as the conditonal format, I know it is something like IFBLANK, "0" or
something, but I'm not sure the exact format, could you help me out?
Thanks again

Jacob

Ron Rosenfeld said:
Jacob,

After reviewing your worksheet, I'm not sure why you were getting the errors.
The worksheet you sent did not have the modified event macro to include the
extra columns and I did not get any errors with the macro that was included in
that.

I modified it to include the two extra columns and it seems to work OK. The
modified routine is below and seems to work OK on the worksheet you sent me.

In addition, I the formula for Total Fees may be incorrectly including the
percent FE fee in the total. Your formula is =SUM(I7:K7). Should it not be
=I7+K7? If you add in J7, you'll also be adding in the percent FE fees.

I would also suggest a Conditional Format to make the DIV/0 errors invisible.

Well, see if this works. If it gives errors, send back a copy of the worksheet
that gives the errors.

===========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sales As Range
Dim CommissionDollar As Range
Dim CommissionPercent As Range
Dim YSPDollar As Range
Dim YSPpercent As Range
Dim RowIndex As Integer
Dim Msg As String

Set Sales = [H7:H65000]
Set CommissionDollar = [I7:I65000]
Set CommissionPercent = [J7:J65000]
Set YSPDollar = [K7:K65000]
Set YSPpercent = [L7:L65000]

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

RowIndex = Target.Row - Sales.Row + 1

On Error GoTo ErrorHandler
If Not Intersect(Target, CommissionDollar) Is Nothing Then
CommissionPercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, CommissionPercent) Is Nothing Then
CommissionDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, YSPDollar) Is Nothing Then
YSPpercent(RowIndex, 1) = Target / Sales(RowIndex, 1)
ElseIf Not Intersect(Target, YSPpercent) Is Nothing Then
YSPDollar(RowIndex, 1) = Target * Sales(RowIndex, 1)
ElseIf Not Intersect(Target, Sales) Is Nothing Then

If Target.Value = 0 Then
CommissionPercent(RowIndex, 1).ClearContents
CommissionDollar(RowIndex, 1).ClearContents
ElseIf CommissionDollar(RowIndex, 1) <> 0 Then
CommissionPercent(RowIndex, 1) = CommissionDollar(RowIndex, 1) / Target
ElseIf CommissionPercent(RowIndex, 1) <> 0 Then
CommissionDollar(RowIndex, 1) = Target * CommissionPercent(RowIndex, 1)
End If

If Target.Value = 0 Then
YSPpercent(RowIndex, 1).ClearContents
YSPDollar(RowIndex, 1).ClearContents
ElseIf YSPDollar(RowIndex, 1) <> 0 Then
YSPpercent(RowIndex, 1) = YSPDollar(RowIndex, 1) / Target
ElseIf YSPpercent(RowIndex, 1) <> 0 Then
YSPDollar(RowIndex, 1) = Target * YSPpercent(RowIndex, 1)
End If

End If

Application.EnableEvents = True

Exit Sub

ErrorHandler:
'Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
'MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

MsgBox ("Data Entry Error")

Application.EnableEvents = True

End Sub
============================================

--ron
Click to expand...
 
R

Ron Rosenfeld

Ron:

I don't know why I received that error message earlier, but i recopied the
below code and it works perfectly. That is awesome, thank-you.

As far as the conditonal format, I know it is something like IFBLANK, "0" or
something, but I'm not sure the exact format, could you help me out?
Thanks again

Jacob
Click to expand...

Jacob,

I returned your workbook with the conditional and other formatting changes in
there.

The CF code for the div/0 error was, if I recall correctly, something like:

=ERROR.TYPE(cell_ref) = 2 (where cell_ref is the cell being formatted).

Then format the font color to be the same as the background color (white in
some instances, light gray in another column).

In addition, you had a bunch of '0' results in one of the colums. I used a
custom number format to not display the 0 at all. I believe you had a number
format with two decimals in that column; I merely added a trailing semi-colon
to the format which eliminates display of the 0 in that cell.

Finally, don't neglect to check on the addition function in the total fees
column.


--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Query problem 1
Excel Prob.....What functions? 2
Reverse Fees Linear Formula 1
Pivot Table & Grand Total Row & XL 2003 1
Using SUMIFs on a changing Pivot Table - Help! 2
Curve fitting with weighted trendline 1
Revenue divided by Day How? 4
Percentage Discount 3

Top