Function to count number of rows and insert a value

attach a sample sheet with explanation.

Lets say I have a list and every 10 rows I want to insert a certain value. What formual would I use to count the rows and insert the value?

In the attachment, there are existing formulas that I want to add to.

Basically, the item highlighted in yellow, I want to be the interval date, so whatever number I put in there, I want it to update column "C" with the interval.
At the moment it only updates on an every other day basis, but if I want the dosing to be once per week or twice per week, I want to add to the formula in "C" that will count the number of rows from the number in "G4" and add the dose in that interval.

Thanks

Ryan

Originally Posted by NBVC

Try, In C3:

=IF(COUNTIF($C$2:C2,"="&$G$2)<$G$3,IF(MOD(ROW(A2),$G$4)=1,$G$2,""),"")

copied down

This does work, except if I make the dosage interval 1, then it doesn't return any values.

=IF(COUNTIF($C$2:C2,"="&$G$2)<$G$3,IF(MOD(ROW(A2),$G$4)=1,$G$2,""),"")

Please can you explain how the highlighted items are working.
In the Mod Row function, I'm not sure why it is equal to 1-when does the remainder become 1?

Appreciate the help

Originally Posted by NBVC

Try instead, then:

Please Login or Register  to view this content.

note, I also changed Row(A2) to Row()-1... same in this case...

This part:

COUNTIF($C$2:C2,"="&$G$2) counts how many times from the top of the column to the the row before the current row contain the dosage amount... this is to determine if you have hit the number of dosages allowed by the value in G3.. if the limit has been reached, then a blank is returned...

If the number of dosages counted is less than your value in G3, then the next part takes over.. OR($G$4=1,MOD(ROW()-1,$G$4)=1

the Mod() function returns the remainder when arg 1 is divided by arg 2... so the only time that is 1 is when the row() number divided by the value in G4 gives a remainder of 1. e.g. if you are in in Row 5 and your interval is set to 3, then Row()-1 gives 4 and 4 divided by 3 is 1 with remainder 1.. same if you are in Row 8, then Row()-1 is 7 and 7 divided by 3 is 2 remainder 1...and so on...

Thanks-much appreciated. I'll have to play with formulas as I haven't worked with mod and row functions before.

Appreciate the help-works great

When I put this in it seems to mess up my other formula-see the yellow tab sheet and the column in dark red.

The idea behind this formula was to calculate the cumulative escalting and de escalating blood concentrations of the drug-see this thread
http://www.excelforum.com/excel-gene...functions.html

Can't seem to figure out what's wrong

That seems to do the trick-not sure what was wrong before. Thanks appreciate the help.
Will mark thread as "solved"

I've been playing the sheet and it doesn't seem to calc dosage intervals correctly with the de escalating dosage as the drug leaves you blood.

On the sheets attached the colum D calculates the cumulative factor growth of the blood concentration and after all doses have been administered, there should be a de escalating portion. This count until 0 should be equal to 2* half life of the drug, but if a dosage interval of 1 is used it isn't and if a high interval is used, the blood concentrations go to zero while the drug is still being ingested.

I've played around but can't seem to come right.

Appreciate any further help

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