software

Dear all,

How to count the number of integers when sum of those, calculated from
left to right, meets a predefined target

E.g:
Range: 50, 100, 200, 100, 150, 300
Target: 450
Function should return 4

Thanks!Hi!

Use a helper row:

Assume your values are in the range A1:F1

In A2 enter this formula:

=SUM($A1:A1)

Copy across to F2

A5 = target value = 450

=INDEX(COLUMN(A2:F2),MATCH(A5,A2:F2,0))

Biff

gt; wrote in message oups.com...
gt; Dear all,
gt;
gt; How to count the number of integers when sum of those, calculated from
gt; left to right, meets a predefined target
gt;
gt; E.g:
gt; Range: 50, 100, 200, 100, 150, 300
gt; Target: 450
gt; Function should return 4
gt;
gt; Thanks!
gt;
Biff wrote...
gt;Assume your values are in the range A1:F1
gt;
gt;In A2 enter this formula:
gt;
gt;=SUM($A1:A1)
gt;
gt;Copy across to F2
gt;
gt;A5 = target value = 450
gt;
gt;=INDEX(COLUMN(A2:F2),MATCH(A5,A2:F2,0))
....

No ancillary cells needed. Also, why the INDEX call? Would the MATCH
call return 4? Indeed, move (*cut* amp; paste) A1:F1 into AA1:AF1. Then
what does your formula return?

Anyway, one single cell alternative would be the array formula

=MATCH(A5,MMULT(A1:F1,--(COLUMN(A1:F1)gt;=TRANSPOSE(COLUMN(A1:F1)))),0)Dear Harlan, thanks for really elegant solution. Please let me ask one
and maybe silly question: what quot;--quot; means in this part of function
--(COLUMN(A1:F1)... ? Why odd numebr of quot;-quot; returnes 6 and even return
4, as far as studued example in concerned?gt;why the INDEX call?

That is kind of superfluous, isn't it?

Biff

quot;Harlan Grovequot; gt; wrote in message oups.com...
gt; Biff wrote...
gt;gt;Assume your values are in the range A1:F1
gt;gt;
gt;gt;In A2 enter this formula:
gt;gt;
gt;gt;=SUM($A1:A1)
gt;gt;
gt;gt;Copy across to F2
gt;gt;
gt;gt;A5 = target value = 450
gt;gt;
gt;gt;=INDEX(COLUMN(A2:F2),MATCH(A5,A2:F2,0))
gt; ...
gt;
gt; No ancillary cells needed. Also, why the INDEX call? Would the MATCH
gt; call return 4? Indeed, move (*cut* amp; paste) A1:F1 into AA1:AF1. Then
gt; what does your formula return?
gt;
gt; Anyway, one single cell alternative would be the array formula
gt;
gt; =MATCH(A5,MMULT(A1:F1,--(COLUMN(A1:F1)gt;=TRANSPOSE(COLUMN(A1:F1)))),0)
gt;