[EASY, SUM=array] Took too long to find this solution and I know it sucks please help

[George_Martin_3]

Gosh I want to be more efficient and I am trying.

I had no idea how to turn the sum of the row(s) into a boolean statement. Otherwise I'd of used sum_product.

My next trick would then be to add this to an aggregate function in order to evaluate order quantities that can filled up to available units assigned criteria.

=IF(SUM($B$4:B4)<=$B$1,B4,IF(0<(B4-ABS($B$1-SUM($B$4:B4))),B4-ABS($B$1-SUM($B$4:B4)),0))

Imgur: The most awesome images on the Internet

B1=27
B4:B11={8,8,8,8,8,8,8,8}

For the life of me I have no idea why I could not google anything close to answer I've generated.

 

[Aladin Akyurek]

An image is not helpful. In order to post an Excel readable, small sample, you might want to try instead one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[George_Martin_3]

<tbody>
[TD="bgcolor: #909090"] I HAVE MONEY FOR YOU! =
[/TD]
[TD="bgcolor: #909090"] 27
[/TD]
[TD="bgcolor: #909090"] DOLLARS!
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090, colspan: 2"] EVERY TIME YOU SAW ME YOU ASKED FOR MONEY, WTF? AND LIKE A GOOD FRIEND I GAVE IT TO YOU.
[/TD]
[TD="bgcolor: #909090"]
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"] U ASKED FOR
[/TD]
[TD="bgcolor: #909090"] I GAVE YOU
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] MONDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] TUESDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] WEDNESDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] THURSDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] FRIDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] SATURDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"] SUNDAY
[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 3
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"] 4
[/TD]
[TD="bgcolor: #909090"] 0
[/TD]
[TD="bgcolor: #909090"]

[/TD]
[TD="bgcolor: #909090"]

[/TD]

</tbody>

Cell C4 down =IF(SUM($B$4:B4)<=$B$1,B4,IF(0<(B4-ABS($B$1-SUM($B$4:B4))),B4-ABS($B$1-SUM($B$4:B4)),0))

Thank you.

 

[Peter_SSs]

Would it be this, copied down?

Excel Workbook
	B	C
1	27	DOLLARS!
2
3	U ASKED FOR	I GAVE YOU
4	4	4
5	4	4
6	4	4
7	4	4
8	4	4
9	4	4
10	4	3
11	4	0
12
Sheet5

 

[George_Martin_3]

No sorry friend it is not. Basically it is how many of a fixed value can I match for values less than the fixed one for every Nth instance. I have 4 dollars, first time you ask for 2, I give you two, next time you ask for 5, I give you three. How much for every Nth instance.

 

[Peter_SSs]

No sorry friend it is not. Basically it is how many of a fixed value can I match for values less than the fixed one for every Nth instance. I have 4 dollars, first time you ask for 2, I give you two, next time you ask for 5, I give you three. How much for every Nth instance.

Sorry, I don't understand that.
Can you give further sample data AND expected output and explain why that output is expected.