Thread: Function to count number of rows and insert a value

Originally Posted by NBVC

I work better if I see sample outcomes...

Can you add a couple more sheets with different sample inputs (esp. the ones you have noted above) and then fill in columns C and D manually with what you expect to see....

Please see attached.

Thanks

Ryan

This is the formula I came up with

=IF(OR($C4<>"",(ROWS($C$2:$C4)<=$G$1*2)),($C4/($G$1*2))+D3,0),IF(OR($C4=" ",(ROWS($C$2:C$4)<=$G$1*2)),D3,0)

but it is giving a value error. can anyone see what I'm doing wrong-the logic of this seems to make sense to me.

Thanks

Ryan

I managed to get it to work, but it doesn't return the values I want.

The problem is that the count has to be done not from the beginning, but from each time there is a value, so if there is a value in c1, the count of the half life * 2 must start from c1 for that array, then if the value is in c5, another count must start from c5.

I can't seem to think of anything, but there must be a way

Originally Posted by NBVC

Starting at D2, Try replacing occurances of $C$2 with:

INDEX($C$2:C2,MATCH(9.9999999E+307,$C$2:C2))

this will index the cell that contains the last occurance of a number from C2 to your current row.. making it the start point...

Based on that, this is the formula that I have come up with, but am getting an error message

=IF(AND($C3<>" ";(ROWS((INDEX($C$2:$C3;MATCH(9.9999999E+307;$C$2:$C3)):$C3)<=$G$1*2));($C3/($G$1*2)+D2));IF(AND($C3=" ";(ROWS((INDEX($C$2:$C3;MATCH(9.9999999E+307;$C$2:$C3)):$C3<=$G$1*2))));D2;0))

Hows does the index match that you have suggested index it from the last occurance, for interests sake?

Thanks

Ryan

Originally Posted by NBVC

INDEX($C$2:C2,MATCH(9.9999999E+307,$C$2:C2))

Match(9.9999999e+307,$C$2:C2) find the position of the last numeric entry in range $C$2:C2 by looking for 9.999999e+307 (which is largest Excel Number)... since it kind find that particular number it returns position of last entry that is smaller than it.. which is, in your case, the dosage in column C.... The Index() part uses that match position to return the actual cell reference containing it.. and so forms a range start and along with your defined range end can be used to reference a dynamic range....

BTW: Have you looked at deadlyduck's last offering (Post # 18)

THanks-I was wondering about the 9.999999e+307.

I've had a look and his post and have been chatting to him by PM, but are still struggling to come up with something that returns the correct values for all ranges.

Does my formula look ok or is there something obvious you can see why it's giving me an error message? It's giving me a parameter error.
I would post the spreadsheet, but have just got a new pc and am having problems with the microsoft key, so am using OpenOffice at the moment until I get it sorted.

Thanks Brian-really appreciate all your help and both formulas do the trick-thanks NBVC.

I am going through it to understand what you are doing.

I have also just started using index match instead of vlookups and it seems a lot more versatile.

Thanks again all-much appreciated