Excel - Use formula/calculation within a Number Format

user_8783311 0
Viewed 1356

How do I put a formula in a Custom Number Format in Excel? For example (random example for illustration purposes),

Assume I have the following data:

INPUT

enter image description here

OUTPUT

Without editing the actual data in the cells, I would like to display the value in the cell divided by 2, and with two decimal places:

enter image description here

Is there a way to do this directly in the Custom Number Format, i.e. Without adding helper columns/cells doing the division calculation. NB. the part that has me stumped is doing the division calculation in the Number Format

Thanks!

number-formatting excel-formula excel
2 Answers

The answer is NO, if you want to do it through custom format in a cell directly.

However, there is workaround in pivot table using calculated field option as shown below:

Calculated Field

By the way I am curious why you want to show the number in a value other than its true value in a cell? This is not a good practice which could lead to misuse of the data in my humble opinion.

user_117683640

I have been wanting to display my min/60 as minutes, but the decimal is necessary for calculations. The best way I've found so far is to use conditional formatting in fractions.

0/60 "min" would display. 21666666 as 13/60 min Which is what the cell formula was. It's only useful for around an hours worth of time, but if I come up with something to more clearly display a calculation of such as 8.5hrs as 8hrs 30min I'll update.

user_198575850
Related