Forum Discussion
mockrom
Sep 27, 2023Copper Contributor
Trying to get all formulas into one cell
GOAL - Trying to get all formulas into 1 cell – B8 SEE BELOW TABLES -If B13 is greater than B6 then B8 s/b 100% -B8 should also have percentage from B7 divided by B5 -Already calculated in ...
NikolinoDE
Sep 27, 2023Gold Contributor
To consolidating all the calculations into cell B8 in Excel 365, you can use a combination of functions and logical statements. You want to calculate the occupancy percentage based on certain conditions. Here is a formula you can use:
=IF(B6>B13, B5-B7, IF(B13>B6, B7/B5, 100%))
Let me break down how this formula works:
- If B6 is greater than B13 (MOs more than MIs), it calculates the occupancy as (B5 - B7).
- If B13 is greater than B6 (MIs more than MOs), it calculates the occupancy as (B7 / B5).
- If B6 and B13 are equal, it returns 100% occupancy.
This formula takes into account all your conditions and calculates the occupancy percentage in a single cell (B8).
Here is how you can apply it:
- Select cell B8.
- Enter the formula: =IF(B6>B13, B5-B7, IF(B13>B6, B7/B5, 100%)).
- Press Enter.
Now, B8 will display the calculated occupancy percentage based on the conditions you specified, without the need for additional rows or formulas.
If you want to consolidate all the formulas into a single cell (B8) without displaying intermediate results in other cells, you can use Excel's formula concatenation feature. This feature allows you to combine multiple formulas into one cell. Here's how you can do it for your scenario:
- Select cell B8.
- Enter the following formula:
="If MO's more than MI's (MO-MI) " & IF(B6 > B13, SUM(B6-B14), IF(B13 > B6, B7-B5, "0")) & ", Total Units Occupied " & IF(B6 > B13, B5-B7, IF(B13 > B6, B7/B5, "100%"))
This formula combines the two calculations and includes a description for each part.
- Press Enter.
Now, cell B8 will display a single text string that includes both calculations. The text will read something like:
"If MO's more than MI's (MO-MI) -2, Total Units Occupied 61%"
This way, you have all the calculations consolidated into one cell (B8) with descriptions for each part of the formula.The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.