Creating a formula to generate the difference between two of three cells, depending on which is greater

[Calientegail2]

Hello! I've built a spreadsheet that needs some tweaking and I need help. Here is what I have:
Cell 1-3 are just identifying data.

Cell 4 Cell 5 Cell 6 Cell 7
$1250. $1300. $1200. ?

The formula is needed for Cell 7. Currently, the formula generates the difference between Cell 4 and Cell 5. I need it to show the difference between Cell 4 and Cell 5 OR Cell 6, depending on which one is greater.

Example: Currently cell 5 is higher, so Cell 7 should be showing $50. But if I increase Cell 6 to $1400, I need the formula to recognize that and generate $150 in Cell 7.


The current formula in Cell 7 is:
=IF(Cell 1=" ", " ", Cell 5-Cell 4)

I'd really appreciate any help!

 

[arthurbr]

Perhaps =If(Cell1="","",MAX(ABS(cell5-cell4),ABS(cell6-cell4)))
The ABS provides for values of cell5/6 lower than cell4

 

[Rick Rothstein]

The formula is needed for Cell 7. Currently, the formula generates the difference between Cell 4 and Cell 5. I need it to show the difference between Cell 4 and Cell 5 OR Cell 6, depending on which one is greater.

Example: Currently cell 5 is higher, so Cell 7 should be showing $50. But if I increase Cell 6 to $1400, I need the formula to recognize that and generate $150 in Cell 7.

This seems to suggest that Cell 4 will always be the minimum value among the three cells. If that is correct, then this generic formula (you didn't tell us row or column info, so I used your cell descriptions) should give you what you want...

=MAX(Cell5, Cell6)-Cell4

If, on the other hand, the maximum and minimum values can be in any of the three cells, then you would need this formula instead...

=MAX(Cell4,Cell5,Cell6)-MIN(Cell4,Cell5,Cell6)

 

[Calientegail2]

I see.. yes, Cell 4 is typically smaller than 5 and 6, but sometimes it is not. This spreadsheet is referencing tenant rents at an apartment complex, Cell 4 is their current rent. Cell 5 is the base market rate, Cell 6 is the current asking rate for new people moving in. Typically the tenant's current rent is lower than both market or asking rents, but sometimes it is higher than one or both.

 

[Rick Rothstein]

I see.. yes, Cell 4 is typically smaller than 5 and 6, but sometimes it is not. This spreadsheet is referencing tenant rents at an apartment complex, Cell 4 is their current rent. Cell 5 is the base market rate, Cell 6 is the current asking rate for new people moving in. Typically the tenant's current rent is lower than both market or asking rents, but sometimes it is higher than one or both.

Given that description, and what you wrote originally, I think my first formula should be giving you the answers you want... does it? If not, give several examples of values in Cell4, Cell5 and Cell6 where the formula fails along with the answer you actually want in Cell7 so we can see what you are really trying to do.

 

[Calientegail2]

This worked!!! I'm so happy! You are amazing... thank you!

 

[Calientegail2]

Bummer, that actually didn't work, I guess ABS only generates a positive number, and sometimes a negative number is needed. But thank you...I combined your suggested formula with the one from Rick below and tweaked it a bit, and now it works! Thank you all! Here is the formula now: =IF(cell1=" "," ",cell4-MAX(cell5, cell6))

 

[Calientegail2]

Rick! You were correct, your formula worked. Thank you so much!