Greater then running total.

[Getty]

Hi everyone,
I'm lost on this one and been trying different things but still can't get it to work.

I have a workbook setup, the first sheet pulls totals from other sheets. I have everything working accept one thing.
On the 1st sheet, I need the account balance to increase when there has been +$xx.xx for the day but if there has been -$xx.xx for 1 day or more, I need the previous high maintained.

The 2nd sheet is where I'm pulling most of the first sheets variables from and the 2nd sheet has the following columns.
A - Date
B - Daily Profit/Loss - This is where the user enters the data each day
C - Daily Total - Uses the formula - =IF(OR(ISBLANK(B3)), " ", C2+B3)
D - % Change - Uses the formula - =IF(OR(ISBLANK(B3))," ",(C3-C2)/C2)
E - Cumulative Total % - Uses the formula - =IF(OR(ISBLANK(B3))," ",(C3-$C$2)/$C$2)
F - Account High - Currently uses this formula.... But is only partly right - =IF(C3>C2,C3,C2)

Each formula has been dragged down the column so it will calculate each row.

At the bottom of the spreed sheet there is a 1 row gap and then a row for totals.

This is the Running $ Profit/Loss Total - B319 - Uses the formula - =SUM(B2:B317)
This is the Running Daily Total - C319 - Uses the formula - =C2+B319 (C2 = the starting account balance)

My problem is with F - Account High. I need to be able to keep the previous highest high looking back over all the previous Daily Totals as I need this previous high maintained on the 1st sheet.

My thought was to use "Greater Then" in column F to find it on the 2nd sheet, put this changing value in the Total row at F319 and then grab it at will from the 1st sheet.

But the current formula I'm using only checks back to the previous row to see if the new value is greater then or less then.
So when I get 2 or more -$xx.xx days, it fails to do the job required.


EG

[TABLE="width: 763"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]$ PROFIT / LOSS[/TD]
[TD]DAILY TOTAL[/TD]
[TD]% CHANGE[/TD]
[TD]CUMMULATIVE TOTAL %[/TD]
[TD]ACCOUNT HIGH[/TD]
[/TR]
[TR]
[TD="align: right"]22-Nov-17[/TD]
[TD] [/TD]
[TD="align: right"]$20,000.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23-Nov-17[/TD]
[TD="align: right"]$0.01[/TD]
[TD="align: right"]$20,000.01[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]$20,000.01[/TD]
[/TR]
[TR]
[TD="align: right"]24-Nov-17[/TD]
[TD="align: right"]$83.38[/TD]
[TD="align: right"]$20,083.39[/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]$20,083.39[/TD]
[/TR]
[TR]
[TD="align: right"]25-Nov-17[/TD]
[TD="align: right"]-$99.99[/TD]
[TD="align: right"]$19,983.40[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]-0.08%[/TD]
[TD="align: right"]$20,083.39[/TD]
[/TR]
[TR]
[TD="align: right"]26-Nov-17[/TD]
[TD="align: right"]$106.25[/TD]
[TD="align: right"]$20,089.65[/TD]
[TD="align: right"]0.53%[/TD]
[TD="align: right"]0.45%[/TD]
[TD="align: right"]$20,089.65[/TD]
[/TR]
[TR]
[TD="align: right"]27-Nov-17[/TD]
[TD="align: right"]$219.64[/TD]
[TD="align: right"]$20,309.29[/TD]
[TD="align: right"]1.09%[/TD]
[TD="align: right"]1.55%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: right"]28-Nov-17[/TD]
[TD="align: right"]-$101.30[/TD]
[TD="align: right"]$20,207.99[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]1.04%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: right"]29-Nov-17[/TD]
[TD="align: right"]-$59.00[/TD]
[TD="align: right"]$20,148.99[/TD]
[TD="align: right"]-0.29%[/TD]
[TD="align: right"]0.74%[/TD]
[TD="align: right"]$20,207.99[/TD]
[/TR]
[TR]
[TD="align: right"]30-Nov-17[/TD]
[TD="align: right"]-$25.00[/TD]
[TD="align: right"]$20,123.99[/TD]
[TD="align: right"]-0.12%[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]$20,148.99[/TD]
[/TR]
[TR]
[TD="align: right"]1-Dec-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]





Where you can see $20,309.29 on the 3rd last line, this should be maintained as the previous high in column F until column C once again surpasses this previous high value and column F can start increasing again.

The other thing, how do I get the ever changing "last" row in column F to update in F319 (Total) so its easy to grab for the 1st sheet?

I hope that's clear....

If not, let me know what else I can provide and I'll try.

Thanks for any help.

 

[Scott T]

Is this what you want?

Excel 2010[TABLE="class: grid, width: 800"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]DATE[/TD]
[TD]$ PROFIT / LOSS[/TD]
[TD]DAILY TOTAL[/TD]
[TD]% CHANGE[/TD]
[TD]CUMMULATIVE TOTAL %[/TD]
[TD]ACCOUNT HIGH[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]22-Nov-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$20,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]23-Nov-17[/TD]
[TD="align: right"]$0.01[/TD]
[TD="align: right"]$20,000.01[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]$20,000.01[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]24-Nov-17[/TD]
[TD="align: right"]$83.38[/TD]
[TD="align: right"]$20,083.39[/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]$20,083.39[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]25-Nov-17[/TD]
[TD="align: right"]-$99.99[/TD]
[TD="align: right"]$19,983.40[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]-0.08%[/TD]
[TD="align: right"]$20,083.39[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]26-Nov-17[/TD]
[TD="align: right"]$106.25[/TD]
[TD="align: right"]$20,089.65[/TD]
[TD="align: right"]0.53%[/TD]
[TD="align: right"]0.45%[/TD]
[TD="align: right"]$20,089.65[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]27-Nov-17[/TD]
[TD="align: right"]$219.64[/TD]
[TD="align: right"]$20,309.29[/TD]
[TD="align: right"]1.09%[/TD]
[TD="align: right"]1.55%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]28-Nov-17[/TD]
[TD="align: right"]-$101.30[/TD]
[TD="align: right"]$20,207.99[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]1.04%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]29-Nov-17[/TD]
[TD="align: right"]-$59.00[/TD]
[TD="align: right"]$20,148.99[/TD]
[TD="align: right"]-0.29%[/TD]
[TD="align: right"]0.74%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]30-Nov-17[/TD]
[TD="align: right"]-$25.00[/TD]
[TD="align: right"]$20,123.99[/TD]
[TD="align: right"]-0.12%[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=MAX($C$2:C3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=MAX($C$2:C4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=MAX($C$2:C5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=MAX($C$2:C6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=MAX($C$2:C7)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]=MAX($C$2:C8)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=MAX($C$2:C9)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F10[/TH]
[TD="align: left"]=MAX($C$2:C10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Getty]

Is this what you want?

Excel 2010[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]DATE[/TD]
[TD]$ PROFIT / LOSS[/TD]
[TD]DAILY TOTAL[/TD]
[TD]% CHANGE[/TD]
[TD]CUMMULATIVE TOTAL %[/TD]
[TD]ACCOUNT HIGH[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]22-Nov-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$20,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]23-Nov-17[/TD]
[TD="align: right"]$0.01[/TD]
[TD="align: right"]$20,000.01[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]$20,000.01[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]24-Nov-17[/TD]
[TD="align: right"]$83.38[/TD]
[TD="align: right"]$20,083.39[/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]0.42%[/TD]
[TD="align: right"]$20,083.39[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]25-Nov-17[/TD]
[TD="align: right"]-$99.99[/TD]
[TD="align: right"]$19,983.40[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]-0.08%[/TD]
[TD="align: right"]$20,083.39[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]26-Nov-17[/TD]
[TD="align: right"]$106.25[/TD]
[TD="align: right"]$20,089.65[/TD]
[TD="align: right"]0.53%[/TD]
[TD="align: right"]0.45%[/TD]
[TD="align: right"]$20,089.65[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]27-Nov-17[/TD]
[TD="align: right"]$219.64[/TD]
[TD="align: right"]$20,309.29[/TD]
[TD="align: right"]1.09%[/TD]
[TD="align: right"]1.55%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]28-Nov-17[/TD]
[TD="align: right"]-$101.30[/TD]
[TD="align: right"]$20,207.99[/TD]
[TD="align: right"]-0.50%[/TD]
[TD="align: right"]1.04%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]29-Nov-17[/TD]
[TD="align: right"]-$59.00[/TD]
[TD="align: right"]$20,148.99[/TD]
[TD="align: right"]-0.29%[/TD]
[TD="align: right"]0.74%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]30-Nov-17[/TD]
[TD="align: right"]-$25.00[/TD]
[TD="align: right"]$20,123.99[/TD]
[TD="align: right"]-0.12%[/TD]
[TD="align: right"]0.62%[/TD]
[TD="align: right"]$20,309.29[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10"]F3[/TH]
[TD="align: left"]=MAX($C$2:C3)[/TD]
[/TR]
[TR]
[TH="width: 10"]F4[/TH]
[TD="align: left"]=MAX($C$2:C4)[/TD]
[/TR]
[TR]
[TH="width: 10"]F5[/TH]
[TD="align: left"]=MAX($C$2:C5)[/TD]
[/TR]
[TR]
[TH="width: 10"]F6[/TH]
[TD="align: left"]=MAX($C$2:C6)[/TD]
[/TR]
[TR]
[TH="width: 10"]F7[/TH]
[TD="align: left"]=MAX($C$2:C7)[/TD]
[/TR]
[TR]
[TH="width: 10"]F8[/TH]
[TD="align: left"]=MAX($C$2:C8)[/TD]
[/TR]
[TR]
[TH="width: 10"]F9[/TH]
[TD="align: left"]=MAX($C$2:C9)[/TD]
[/TR]
[TR]
[TH="width: 10"]F10[/TH]
[TD="align: left"]=MAX($C$2:C10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you SO much!

That fixed it perfectly. I then added the following to tidy up the rest of the spreadsheet when the other cells on the row are empty.


=IF(OR(ISBLANK(B3))," ",MAX($C$2:C3))

Exactly what I needed!
Thank you!

 

[Getty]

I should also add the steps I took in case anyone else needs it in the future.

I first entered Scott T's formula into F3.
=MAX($C$2:C3)
Then dragged that formula all the way down to the Total row at F319.

I deleted the formula from F318 to create a blank cell.

Then went back to F3 and added in the rest of the formula so that it will show empty cells if no data is in B column.
=IF(OR(ISBLANK(B3))," ",MAX($C$2:C3))
Then dragged this new formula down to F317.

This way, the Total at F319 is always up to date no matter the row the data is entered down to in column B but any empty rows are not printing cells in column F.

Fixed everything for me and works like a dream!
Thanks again Scott T! I had been going around in circles for a bit over a day with this.