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

[FuriousStyles]

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.

Jan Date | Jan Accounts | Dupe| Count |% to Goal
1/1/2018 0010y00001Xx9c6 1 1 0.39%
1/1/2018 0010y00001Xx9c6 0 1 0.39%
1/1/2018 0011A00001W12Wa 1 2 0.78%
1/2/2018 0011A00001U9Yjl 1 3 1.17%
1/2/2018 0011A00001U9Yjl 0 3 1.17%
1/2/2018 0011A00001T0qpZ 1 4 1.56%
1/2/2018 0011A00001T0qpZ 0 4 1.56%
1/3/2018 0011A00001VU91a 1 5 1.95%
1/3/2018 0011A00001VU91a 0 5 1.95%
1/3/2018 0010y00001XxzH6 1 6 2.33%
1/3/2018 0010y00001XxzH6 0 6 2.33%
1/3/2018 0011A00001UZ0sK 1 7 2.72%
1/3/2018 0011A00001UtFnr 1 8 3.11%
1/3/2018 0011A00001UtFnr 0 8 3.11%

I then used Formula A (below) to create the table below, giving me the max value at the end of day 1 for the % to Goal Column. 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 prior 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)))

Day Jan Feb March April
1 0.78% 5.49% 3.47% 0.71%
2 1.56% 8.06% 4.51% 2.49%
3 3.11% 8.42% 0.00% 3.56%
4 5.06% 0.00% 5.21% 4.98%
5 8.17% 12.45% 6.60% 7.12%
6 8.56% 13.92% 8.68% 9.61%
7 0.00% 17.58% 12.50% 0.00%
8 10.12% 22.34% 16.67% 9.96%
9 12.45% 26.37% 19.10% 13.88%
10 15.56% 26.74% 0.00% 20.28%
11 20.23% 27.11% 0.00% 23.49%
12 23.35% 30.77% 21.53% 24.91%
13 0.00% 32.60% 23.61% 0.00%
14 0.00% 34.43% 24.31% 0.00%
15 28.02% 39.19% 25.69% 0.00%
16 31.52% 42.49% 28.47% 0.00%
17 36.19% 0.00% 0.00% 0.00%
18 40.08% 0.00% 28.82% 0.00%
19 42.80% 43.59% 30.90% 0.00%
20 0.00% 46.15% 34.72% 0.00%
21 0.00% 48.72% 40.63% 0.00%
22 46.69% 51.28% 45.83% 0.00%
23 52.53% 55.68% 50.69% 0.00%
24 57.59% 56.04% 0.00% 0.00%
25 60.70% 0.00% 0.00% 0.00%
26 66.54% 61.17% 56.60% 0.00%
27 0.00% 67.40% 60.07% 0.00%
28 0.00% 83.88% 65.97% 0.00%
29 69.65% 0.00% 73.96% 0.00%
30 78.60% 0.00% 86.46% 0.00%
31 90.27% 0.00% 89.24% 0.00%



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

[Pete_UK]

It would help if you attached a sample Excel workbook. (i.e. not a picture of one, or a link to another site)

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon (Attachments button) does not work on this forum, so don't try to use that.

Hope this helps.

Pete

[FuriousStyles]

Hi Pete - i posted my workbook. The table I mentioned in the post is in the FEEDER FOR DASH sheet.

[Pete_UK]

You could put this array* formula in cell B2 of the Feeder sheet:

=IFERROR(1/(1/(MAX(IF(DAY(All!A2:A403) = A2, All!E2:E403)))),B1)

*Note that you need to commit the formula using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >.

and then copy down, so it will return the previous value if the calculated value is zero. You can make similar changes to the formulae for the other months.

However, it will not work properly if the 1st of a month is either a Saturday or Sunday (i.e. with no data), as this should really be getting data from the end of the previous column (for Feb / March etc.), and I am not sure what you want to return if the 1st January is either a Saturday or a Sunday.

Hope this helps (for now).

Pete

[Pete_UK]

You can put the following array* formulae in the cells stated:

B2: =IFERROR(1/(1/(MAX(IF(DAY(All!A2:A403) = A2, All!E2:E403)))),IF(ROW(B2)=2,"",B1))

C2: =IF(ISERROR(DATEVALUE($A2&C$1&2018)),"",IF(MAX(IF(DAY(All!F$2:F$500) = $A2, All!J$2:J$500))=0,IF(AND(WEEKDAY(DATEVALUE($A2&C$1&2018),2)>5,ROW(C2)=2),LOOKUP(100,B$2:B$32),C1),MAX(IF(DAY(All!F$2:F$500) = $A2, All!J$2:J$500))))

D2: =IF(ISERROR(DATEVALUE($A2&D$1&2018)),"",IF(MAX(IF(DAY(All!K$2:K$500) = $A2, All!O$2:O$500))=0,IF(AND(WEEKDAY(DATEVALUE($A2&D$1&2018),2)>5,ROW(D2)=2),LOOKUP(100,C$2:C$32),D1),MAX(IF(DAY(All!K$2:K$500) = $A2, All!O$2:O$500))))

E2: =IF(ISERROR(DATEVALUE($A2&E$1&2018)),"",IF(MAX(IF(DAY(All!P$2:P$500) = $A2, All!T$2:T$500))=0,IF(AND(WEEKDAY(DATEVALUE($A2&E$1&2018),2)>5,ROW(E2)=2),LOOKUP(100,D$2:D$32),E1),MAX(IF(DAY(All!P$2:P$500) = $A2, All!T$2:T$500))))

*Use CSE to commit, as previously advised

and then copy these down. The first one will return a blank for the first cell if 1/01/2018 is a weekend. The other formulae will pick up the final value in the previous column if the first of the month occurs at a weekend. Otherwise they will all pick up the previous value in the column if there is no activity on a weekend day. Note that the formulae also take account of the number of days in the month, so you get blanks after 28th Feb and after 30th April.

Hope this helps.

Pete