EXCEL - Minimum range of cells such that the sum is greater than a target number 06Sep2020 FINAL.xlsx | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||

2 | Week1 | Week2 | Week3 | Week4 | Week5 | Week6 | Week7 | Week8 | Week9 | Week10 | Week11 | Week12 | Week13 | Week14 | Week15 | TOTAL | Sum to Look for | |||

3 | Sales Qty --> | 155 | 95 | 109 | 159 | 68 | 86 | 73 | 119 | 102 | 74 | 48 | 88 | 94 | 195 | 160 | 1625 | 813 | ||

Sheet1 (2) |

Cell Formulas | ||
---|---|---|

Range | Formula | |

R3 | R3 | =SUM(C3:Q3) |

S3 | S3 | =ROUNDUP(50%*R3,0) |

Further illustration can be seen below:

EXCEL - Minimum range of cells such that the sum is greater than a target number 06Sep2020 FINAL.xlsx | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||

7 | ||||||||||||||||||||||

8 | 155 | 95 | 109 | 159 | 68 | 86 | 73 | 119 | 102 | 74 | 48 | 88 | 94 | 195 | 160 | Finding Sum >= 813 | No. of cells such that >=813 | Remarks | ||||

9 | 155 | 155 | 250 | 359 | 518 | 586 | 672 | 745 | 864 | 966 | 1040 | 1088 | 1176 | 1270 | 1465 | 1625 | 864 | 8 | Tie for 8 cell range, but not final answer as 864 is <= 880 | |||

10 | 95 | 95 | 204 | 363 | 431 | 517 | 590 | 709 | 811 | 885 | 933 | 1021 | 1115 | 1310 | 1470 | 885 | 9 | |||||

11 | 109 | 109 | 268 | 336 | 422 | 495 | 614 | 716 | 790 | 838 | 926 | 1020 | 1215 | 1375 | 838 | 9 | ||||||

12 | 159 | 159 | 227 | 313 | 386 | 505 | 607 | 681 | 729 | 817 | 911 | 1106 | 1266 | 817 | 9 | |||||||

13 | 68 | 68 | 154 | 227 | 346 | 448 | 522 | 570 | 658 | 752 | 947 | 1107 | 947 | 10 | ||||||||

14 | 86 | 86 | 159 | 278 | 380 | 454 | 502 | 590 | 684 | 879 | 1039 | 879 | 9 | |||||||||

15 | 73 | 73 | 192 | 294 | 368 | 416 | 504 | 598 | 793 | 953 | 953 | 9 | ||||||||||

16 | 119 | 119 | 221 | 295 | 343 | 431 | 525 | 720 | 880 | 880 | 8 | Tie for 8 cell range, this is final answer as 880 is >= 864 | ||||||||||

17 | 102 | 102 | 176 | 224 | 312 | 406 | 601 | 761 | 0 | 0 | ||||||||||||

18 | 74 | 74 | 122 | 210 | 304 | 499 | 659 | 0 | 0 | |||||||||||||

19 | 48 | 48 | 136 | 230 | 425 | 585 | 0 | 0 | ||||||||||||||

20 | 88 | 88 | 182 | 377 | 537 | 0 | 0 | |||||||||||||||

21 | 94 | 94 | 289 | 449 | 0 | 0 | ||||||||||||||||

22 | 195 | 195 | 355 | 0 | 0 | |||||||||||||||||

23 | 160 | |||||||||||||||||||||

24 | ||||||||||||||||||||||

Sheet1 (2) |

Cell Formulas | ||
---|---|---|

Range | Formula | |

C9 | C9 | =C8 |

P9:Q21,Q22,O9:O20,N9:N19,M9:M18,L9:L17,K9:K16,J9:J15,I9:I14,H9:H13,G9:G12,F9:F11,E9:E10,D9 | P9 | =O9+P$8 |

R9:R22 | R9 | =MIN(IF(D9:Q9>=$S$3,D9:Q9,"")) |

S9:S22 | S9 | =IF(R9=0,0,COUNTA($C9:INDEX(C9:Q9,0,MATCH(R9,C9:Q9,0)))) |

P22 | P22 | =P8 |

D10 | D10 | =D8 |

E11 | E11 | =E8 |

F12 | F12 | =F8 |

G13 | G13 | =G8 |

H14 | H14 | =H8 |

I15 | I15 | =I8 |

J16 | J16 | =J8 |

K17 | K17 | =K8 |

L18 | L18 | =L8 |

M19 | M19 | =M8 |

N20 | N20 | =N8 |

O21 | O21 | =O8 |

Press CTRL+SHIFT+ENTER to enter array formulas. |