Trying to avoid nested IF statements....

[Accessdabbler]

Not sure the best way to explain this. Consider a row of numbers:

100 200 500 300 400

Now, in another cell I type the value "1000".

I want a formula that will subtract the first row of numbers, one-by-one from 1000 until the result is less than 0. In this case, the answer would be 4 since the formula had to go to the 4th number before the result was less than 0.

If it makes a difference, the numbers could be in a column instead of a row.

 

[PHV]

A starting point (UDF):

Function GetNumber(a As Double) As Integer
Const refRow = 1
Dim col As Integer, rest As Double
col = 1: rest = a
Do
  rest = rest - Cells(refRow, col)
  col = col + 1
Loop While rest > 0 And Cells(refRow, col) <> ""
GetNumber = col - 1
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Accessdabbler]

How do I enter this into Excel?

 

[SkipVought]

Paste the cod into a MODULE.

Use this function on your sheet, as you would any other function.

Your row of data must begin in A1. Your 1000 or whatever number you choose, can be entered directly as an argument or can be in a cell and be a refernce in the function argument.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]