Formatting decimal places based on text in another cell.

mandareds2004

New Member
Joined
Dec 21, 2004
Messages
2
I am trying to modify the decimal places based on the text of another cell:

Cell A140 can read either:
Line 1 - "Price to Achieve Financial Goals ($/kWh):"
Line 2 - "Price to Achieve Financial Goals ($/million Btu):"
Line 3 - "Price to Achieve Financial Goals ($/gallon):"

Cell E140 reads different cells based on the correct units:
Cell 'INP-OUT'!$G$51 is in E140 if A140 is Line 1
Cell 'INP-OUT'!$G$50 is in E140 if A140 is Line 2
Cell 'INP-OUT'!$G$49 is in E140 if A140 is Line 3

If the contents of cell A140 equals line 1, I want the result of the equation in cell E140 to show 4 decimal places, with precision: (e.g. 0.3520)

If the contents of cell A140 equals line 2, I want the equation in cell E140 to be 2 decimal places and comma separated with precision: (e.g. 1,000.26)

If the contents of cell A140 equals line 1, I want the equation in cell E140 to be 3 decimal places with precision: (e.g. 0.210)

I have tried a Macro I saw also on this site (see below) but I can't get the commas to work. I am open to a different approach as well if someone suggests it.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim fmt As String
If Target.Address(0, 0) = "A140" Then
If Target.Value = "Price to Achieve Financial Goals ($/kWh):" Then
fmt = "0.0000"
End If
If Target.Value = "Price to Achieve Financial Goals ($/million Btu):" Then
fmt = "#,##0.00"
Else
fmt = "0.000"
End If
Range("E140").NumberFormat = fmt
End If
End Sub

Thanks!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Welcome to MrExcel Board!

How about using a Select Case statment?:

Something like:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Range("A140")) Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "Price to Achieve Financial Goals ($/kWh):":
            Target.Offset(, 4).NumberFormat = "0.0000"
        Case "Price to Achieve Financial Goals ($/million Btu):"
            Target.Offset(, 4).NumberFormat = "#,##0.00"
        Case "Price to Achieve Financial Goals ($/gallon):"
            Target.Offset(, 4).NumberFormat = "0.000"
    End Select

End Sub

Hope that helps!
 

mandareds2004

New Member
Joined
Dec 21, 2004
Messages
2
Formatting Decimal Points based on text of another cell

Hi thanks for the reply and welcome!

Your Case statement seems to work fine for the decimal places but no comma formatting seems to occur. instead i get a number formated like:

1348.0

I am using Excel 2002, is the "##,#.0" the correct format for this version?
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
They both work for me in XP and 2003. I'd suggest using the macro recorder to record formatting a cell exactly how you want it, and use that format in your code.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,806
Messages
5,833,778
Members
430,232
Latest member
Testsubject

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top