Please help w/formula

[pballard]

Please help if possible. I do appreciate.

I will attempt to explain as best I can.

Column L is my High Watermark (HWM) Values.
Cell L4 contains a pre-determined value and the formula for Column L begins in L5.
Column L formula is: =SUM(L4+D5-E5-G5-H5-I5-J5+K5) and this formula is copied for all of Column L.

Column M is my Adjusted HWM Values.
The formula for Column M begins in cell M5.
Column M formula is: =SUM(C5-D5+E5+G5+H5+I5+J5-K5) and this formula is copied for all of Column M.

The condition I need is that if the value in cell M5 (which would be our current quarter) is greater than the value in cell L4 (which would be the previous quarter), then both cells M5 and L5 should show value of cell M5. If the value in cell L4 is greater than the value in cell M5, then the formula (L4+D5-E5-G5-H5-I5-J5+K5) would apply in cell L4.

I would attach an example but don't see where this feature is available.

Say the value in cell L7 is $194,239.50 and M8 is $191,092.32. Since L7 is greater than M8, the formula (L7+D8-E8-G8-H8-I8-J8+K8) would apply in cell L8.

I hope this makes sense.

Pamela

 

[asad]

May be something like this

=max(SUM(L4+D5-E5-G5-H5-I5-J5+K5),SUM(C5-D5+E5+G5+H5+I5+J5-K5))

in L4 and copy down.

 

[Scott Huish]

If you are going to use + and - the SUM is not necessary.

=max(L4+D5-E5-G5-H5-I5-J5+K5,C5-D5+E5+G5+H5+I5+J5-K5)

 

[pballard]

May be something like this

=max(SUM(L4+D5-E5-G5-H5-I5-J5+K5),SUM(C5-D5+E5+G5+H5+I5+J5-K5))

in L4 and copy down.

I tried that and it didn't work.

If L7 is $194,239.50 and M8 is $191,092.32 then I need the formula (L7+D8-E8-G8-H8-I8-J8+K8) to apply and in column E8 is $5,182 which would mean L8 should be $189,057.50.

Pamela

 

[pballard]

I tried that one as well and it didn't work.

I tried that and it didn't work.

If L7 is $194,239.50 and M8 is $191,092.32 then I need the formula (L7+D8-E8-G8-H8-I8-J8+K8) to apply and in column E8 is $5,182 which would mean L8 should be $189,057.50.

 

[Scott Huish]

If the value in cell L4 is greater than the value in cell M5, then the formula (L4+D5-E5-G5-H5-I5-J5+K5) would apply in cell L4.

You can't have a value and replace it with a different value or formula without VBA.

Here is your sheet per your latest post. What is supposed to happen exactly?

Excel Workbook
	E	F	G	H	I	J	K	L	M
6
7								$194,239.50
8	$5,182.00								$191,092.32
Sheet1