Math Help!

[tobermory]

Hi there,

Please take a look at my sample spreadsheet I've imported to google. I do use Excel 2016, I've just imported for you guys to view.

https://docs.google.com/spreadsheets/d/1C2frEj0Lmzghy5nyDJxOgD57hjeORUEf5V183jCpMKA/edit?usp=sharing

I need a formula/code that calculates the following questions...

H2 - How many boxes do I need to collect to fulfill collecting 7 apples (F2) using the LOWEST qtys first? (i.e. 1+2+2+2 = 7 - 4 Boxes)

I2 - How many boxes do I need to collect to fulfill collecting 7 apples (F2) using the HIGHEST qtys first? (i.e. 5+5 = 10 - 2 Boxes)

and then so forth for the following fruits/qtys listed...

Thank you so much for any help in advance!

 

[barry houdini]

Hello tobermory,

Assuming you are using Excel and also assuming the fruits will be grouped and listed in order as per your example then try this array formula in H2

=IF(F2>SUMIF(B:B,E2,C:C),"Too high",IFERROR(MATCH(F2-1,SUMIF(OFFSET(B$1,MATCH(E2,B:B,0)-1,0,ROW(INDIRECT("1:"&F2))),E2,INDEX(C:C,MATCH(E2,B:B,0)))),0)+1)

and this one in I2

=IF(F2>SUMIF(B:B,E2,C:C),"Too high",IFERROR(MATCH(F2-1,SUMIF(OFFSET(B$1,MATCH(E2,B:B)-1,0,-ROW(INDIRECT("1:"&F2))),E2,OFFSET(C$1,MATCH(E2,B:B)-1,0,-ROW(INDIRECT("1:"&F2))))),0)+1)

both confirmed with CTRL+SHIFT+ENTER and copied down

I get the same results as you except for I3 where I get 3 for Banana using highest (5+5+4)

 

[tobermory]

Brilliant!! Thanks very much!

 

[tobermory]

This may be too much to ask but...

Would there be any way to do it if the fruits WASN'T grouped and the qtys not in order?

Thanks sincerely once again.