Trying to get all formulas into one cell

[mockrom]

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 B8 –     =IF(B13>B6,”100%”,B7/B5)

-But if B6 is greater than B13, then the difference s/b subtracted from B6 for the total in B7 – Which will then be calculated in B8

**Still want to identify MI’s and MO’s separately, but calculate the final #’s in Cell 8

**Really do not want to create the New Formula (see pg 2) with another row.

**I want to keep all data within their own cell (As shown in Old Formula) with final results in Cell 8.

OLD FORMULA

	A	B
5	Units	59
6	X Units - Number of Move Outs =	3
7	Total Units Occupied =	56
8	Total Units Occupied divided by X Units =	100%	=IF(B13>B6,"100%",B7/B5)
13	Total Move Ins =	5

	A	B
5	Units	59
6	X Units - Number of Move Outs =	5
7	Total Units Occupied =	54
8	Total Units Occupied divided by X Units =	92%	=IF(B13>B6,"100%",B7/B5)
13	Total Move Ins =	3

NEW FORMULA?

	A	B
5	Units	59
6	X Units - Number of Move Outs =	3
7	If MO's more than MI's (MO-MI)	-2	=sum(B6-B14)
8	Total Units Occupied =	61	=(B5-B7)
9	Total Units Occupied divided by X Units =	100%	=IF(B14>B6,"100%",B8/B5)
14	Total Move Ins =	5

-Do not need a negative number in B7 – s/b zero

-B8 should not go over 59

	A	B
5	Units	59
6	X Units - Number of Move Outs =	5
7	If MO's more than MI's (MO-MI)	2	=sum(B6-B14)
8	Total Units Occupied =	56	=(B5-B7)
9	Total Units Occupied divided by X Units =	95%	=IF(B14>B6,"100%",B8/B5)
14	Total Move Ins =	3

[#2]

@mockrom 

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:

1. Select cell B8.
2. Enter the formula: =IF(B6>B13, B5-B7, IF(B13>B6, B7/B5, 100%)).
3. 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:

1. Select cell B8.
2. 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.

1. 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.

[#3]

@mockrom 

As variant 

=MAX( B13>B6, ( B5-MAX(0, B6-B13) )/B5)

However, not sure I understood the logic correctly. Here

it shall be 57, isn't it?