If statement to Return the previous non zero value in a column

[PatrickHenry]

Hi All,

I have this Google Sheets workbook that is meant to compare sales for the past 4 months. The reports for each month are pulled in automatically from sales force and the 0 values are from the weekends mostly when no sales come in. To compare 4 months on 1 line graph, the lines need to be overlapping and not side by side, I need to show the MAX value at the end of Day 1 for each month on one table.

Example. January Month Report. Pulls in the raw data from column 1 and 2 automatically. The last 3 columns are calculated formulas on the Google Sheet.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="127"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Jan Date[/TD]
[TD]Jan Accounts[/TD]
[TD]Check for Dupes[/TD]
[TD]Jan Count[/TD]
[TD]Jan % to Goal[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]55555555555[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.39%[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]55555555555[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]0.39%[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]45454545454[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.00[/TD]
[TD="align: right"]0.78%[/TD]
[/TR]
</tbody>[/TABLE]

I then used Formula A (below) to create the table below, giving me the max value at the end of day 1 for each month. The issue is that the 0.00% values from the weekends with no sales are causing the line graph to look very odd. I cannot edit the Salesforce reports to bring in calculated fields, only raw data.

I need a formula that IF 0.00% is the result of the Formula A, AND there is a NON ZERO value before then that value is returned. Ex If Day 1 max value for Jan was 0 then 0.00% would be returned. If its Day 14 and the max value of that day in Jan is 0.00% then 23.25% would be returned as it is the most recent non zero value in the Jan column of the below table.

Formula A
=ArrayFormula(MAX(IF(Day(All!P32:P151) = A32, All!T32:T151)))

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 51px"><col width="100"><col width="87"><col width="78"><col width="55"></colgroup><tbody>[TR]
[TD="align: center"]Day[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]March[/TD]
[TD="align: center"]April[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]0.78%[/TD]
[TD="align: center"]5.49%[/TD]
[TD="align: center"]3.47%[/TD]
[TD="align: center"]0.71%[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1.56%[/TD]
[TD="align: center"]8.06%[/TD]
[TD="align: center"]4.51%[/TD]
[TD="align: center"]2.49%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3.11%[/TD]
[TD="align: center"]8.42%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]3.56%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5.06%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]5.21%[/TD]
[TD="align: center"]4.98%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]8.17%[/TD]
[TD="align: center"]12.45%[/TD]
[TD="align: center"]6.60%[/TD]
[TD="align: center"]7.12%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]8.56%[/TD]
[TD="align: center"]13.92%[/TD]
[TD="align: center"]8.68%[/TD]
[TD="align: center"]9.61%[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]17.58%[/TD]
[TD="align: center"]12.50%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]10.12%[/TD]
[TD="align: center"]22.34%[/TD]
[TD="align: center"]16.67%[/TD]
[TD="align: center"]9.96%[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]12.45%[/TD]
[TD="align: center"]26.37%[/TD]
[TD="align: center"]19.10%[/TD]
[TD="align: center"]13.88%[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]15.56%[/TD]
[TD="align: center"]26.74%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]20.28%[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]20.23%[/TD]
[TD="align: center"]27.11%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]23.49%[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]23.35%[/TD]
[TD="align: center"]30.77%[/TD]
[TD="align: center"]21.53%[/TD]
[TD="align: center"]24.91%[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]32.60%[/TD]
[TD="align: center"]23.61%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]34.43%[/TD]
[TD="align: center"]24.31%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]28.02%[/TD]
[TD="align: center"]39.19%[/TD]
[TD="align: center"]25.69%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]31.52%[/TD]
[TD="align: center"]42.49%[/TD]
[TD="align: center"]28.47%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]36.19%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]40.08%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]28.82%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]42.80%[/TD]
[TD="align: center"]43.59%[/TD]
[TD="align: center"]30.90%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]46.15%[/TD]
[TD="align: center"]34.72%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]48.72%[/TD]
[TD="align: center"]40.63%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]46.69%[/TD]
[TD="align: center"]51.28%[/TD]
[TD="align: center"]45.83%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]52.53%[/TD]
[TD="align: center"]55.68%[/TD]
[TD="align: center"]50.69%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]57.59%[/TD]
[TD="align: center"]56.04%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]60.70%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]66.54%[/TD]
[TD="align: center"]61.17%[/TD]
[TD="align: center"]56.60%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]67.40%[/TD]
[TD="align: center"]60.07%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]83.88%[/TD]
[TD="align: center"]65.97%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]69.65%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]73.96%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]78.60%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]86.46%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]90.27%[/TD]
[TD="align: center"]0.00%[/TD]
[TD="align: center"]89.24%[/TD]
[TD="align: center"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]

*I apologize if the explanation is lacking, let me know any additional details you need

 

[PatrickHenry]

=ArrayFormula(IFERROR(1/(1/(MAX(IF(DAY(Jan_Demos!$A$2:$A$40000) = A3, Jan_Demos!$Q$2:$Q$40000)))),B2))

** make sure to have a row of 0% above the first cell in case the first of the month is on a weekend