Excelisfun Needs Your Help in Solving A Formula Number Formatting Mystery.
HTML-код
- Опубликовано: 7 фев 2025
- Download Excel file: people.highlin...
I need help solving a Number Formatting Formula Problem: 1) No cells have been pre=formatted with Currency Number Formatting, 2) Cell with formula has no Number Formatting, 3) But if you create a formula in a cell Currency Number Formatting Appears?!!?!?!?!?!?
Learn #excel #excelformulas #excelfunctions #text #numberformat
All the financial functions format the return values as currency by default. Depreciation functions like DB, SLN, Ddb, vdb, syd also do the same
I’ve noticed excel adding things. Like it automatically changes my negative numbers to red. And if you click in a pivot table data, in the new sheet it has a title above the table
I think it's built into the formula. I don't use many "financial" formulas but I've frequently used PMT to compute monthly mortgage payments, and it always formats it that exact same way.
Formatting happens automatically also in simpler cases e.g. when adding a number to a date, excel formats as date, not as general.
Never saw it either (or at least I did not notice it). I asked copilot and it seems that this is true for the common financial functions like PV, FV, PMT etc.
You ask copilot stuff, even though it gives the wrong answer for some technical stuff? I just asked it and it said:
"No, Excel's financial functions do not use currency number formatting by default. When you use financial functions like PMT, FV, or NPV, the results are typically displayed as general numbers. You can manually apply currency formatting to the cells containing these results if you want them to be displayed as currency.
Would you like to know how to apply currency formatting to your results in Excel?"
Anyway, shame on you for relying on AI for technical stuff, my friend...
O. wait, maybe you have an in that I do not lol
Cool way to do NPV
Probably related to PV formula (and other financial functions).
One solution I could think of is that you create a VBA code, which transforms to "general" format changes caused by a formula, like in your NPV example. Here is the code you can put in a sheet Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False ' Prevent infinite loop
For Each cell In Target
If cell.HasFormula Then
cell.NumberFormat = "General" ' Change format to General
End If
Next cell
Application.EnableEvents = True ' Re-enable events
End Sub
you can then change manually the format to currency, when yu need it. This will not be changed back to general. Cannot upload a scxreenshot, sorry.
Noticed this format "anticipation" after the last update for DATE and TIME functions.
100% stupidity by MS to force Number Formatting on us. Seriously, what the F^&$%^&$ are they thinking?
Hi Mike
Could it be that Excel changes the format due to a financial function to currency?
I think you right. I just did a test in a new workbook with inputs that had no Number Formatting and Currency came up. But why? I never saw this in any Microsoft documentation, and I guess I never thought about it before... What this means is that the rule I have lived by (and thought was in the code from all MS programming): that all content and formulas got General Formatting unless we added formatting, might not be true!?!?! Finance functions like PV, FV, PMT, NPV, XNPV and so on are programmed to apply currency? That seems counter to the rest of how Excel works...
@@excelisfun That's exactly what's going on. As far as I'm concerned, this is a good thing. If you're dealing with money it makes sense to format as currency.
@excelisfun I was going to say that I bet because it's a value of money it's formatting accordingly. It wouldn't make sense for the answer to not be in currency. What's the present value of a tangible object? That object. If you are calculating things and it comes out as money, and it's not supposed to, that's a pretty good visual indication you are doing something wrong. I think it's brilliant.
@@brianxyz yes that’s all very well if you live in a single currency environment, but many of us don’t!
It's silly. How a person formats a report is entirely dependent on numerous other things. They might already have an indicator elsewhere on their worksheet ("all values in $000", for example). It should always draw from the cells being calculated. It's funny, someone actually had to add the feature in when creating the functions. Everyone in the world will apply currency format to this, so I'll save them some time!