Do you think you can help with the following excel problem?<o></o>
<o></o>
I need help with a formula for the number of occurrences at the bottom of the Deposit date column.<o></o>
Based upon the entered data the number should equal the number of highlighted cells which is “3”.<o></o>
The highlights occur through conditional formatting when the date of deposit is 2 or 3 days greater than the batch date. How can I automate this value so that it will automatically filter for the value of highlighted cells? This data will change monthly so I need the formula to read the highlighted cells only whenever they may occur which could be in any cell. Thanks in advance.<o></o>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_3" style="width: 282.6pt; height: 446.4pt;" type="#_x0000_t75" alt="" o:spid="_x0000_i1025"> <v:imagedata o:href="cid:image004.png@01D2BF41.FDE812F0" src="file:///C:\Users\vmoore\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o>
<tbody>
</tbody></o>
<o></o>
I need help with a formula for the number of occurrences at the bottom of the Deposit date column.<o></o>
Based upon the entered data the number should equal the number of highlighted cells which is “3”.<o></o>
The highlights occur through conditional formatting when the date of deposit is 2 or 3 days greater than the batch date. How can I automate this value so that it will automatically filter for the value of highlighted cells? This data will change monthly so I need the formula to read the highlighted cells only whenever they may occur which could be in any cell. Thanks in advance.<o></o>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_3" style="width: 282.6pt; height: 446.4pt;" type="#_x0000_t75" alt="" o:spid="_x0000_i1025"> <v:imagedata o:href="cid:image004.png@01D2BF41.FDE812F0" src="file:///C:\Users\vmoore\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o>
Day | Batch Date | Deposit Date | Amount |
Saturday | 1-Apr | ||
Sunday | 2-Apr | ||
Monday | 3-Apr | [highlight]6-Apr | 256.00[/highlight] |
Tuesday | 4-Apr | 6-Apr | 134.35 |
Wednesday | 5-Apr | 6-Apr | 50.00 |
Thursday | 6-Apr | 7-Apr | 230.35 |
Friday | 7-Apr | 10-Apr | 173.50 |
Saturday | 8-Apr | 10-Apr | 455.00 |
Sunday | 9-Apr | ||
Monday | 10-Apr | [highlight]13-Apr | 195.45[/highlight] |
Tuesday | 11-Apr | 13-Apr | 180.00 |
Wednesday | 12-Apr | 13-Apr | 229.65 |
Thursday | 13-Apr | 14-Apr | 45.62 |
Friday | 14-Apr | [highlight]18-Apr | 40.00[/highlight] |
Saturday | 15-Apr | ||
Sunday | 16-Apr | ||
Monday | 17-Apr | 18-Apr | 57.00 |
Tuesday | 18-Apr | 20-Apr | 314.20 |
Wednesday | 19-Apr | 20-Apr | 13.00 |
Thursday | 20-Apr | 21-Apr | 40.00 |
Friday | 21-Apr | 21-Apr | 15.00 |
Saturday | 22-Apr | ||
Number of Deposit Days | 16 | ||
[highlight]Number of Occurences [/highlight][highlight] | 3[/highlight] | ||
Percentage | 19% |
<tbody>
</tbody>