PatrickHenry
New Member
- Joined
- Dec 12, 2017
- Messages
- 4
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>
<colgroup><col style="width: 100px"><col width="127"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
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>
<colgroup><col style="width: 51px"><col width="100"><col width="87"><col width="78"><col width="55"></colgroup><tbody>
</tbody>
*I apologize if the explanation is lacking, let me know any additional details you need
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>
Jan Date | Jan Accounts | Check for Dupes | Jan Count | Jan % to Goal |
1/1/2018 | 55555555555 | 1 | 1.00 | 0.39% |
1/1/2018 | 55555555555 | 0 | 1.00 | 0.39% |
1/1/2018 | 45454545454 | 1 | 2.00 | 0.78% |
<colgroup><col style="width: 100px"><col width="127"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
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>
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% |
<colgroup><col style="width: 51px"><col width="100"><col width="87"><col width="78"><col width="55"></colgroup><tbody>
</tbody>
*I apologize if the explanation is lacking, let me know any additional details you need