Function to count number of rows and insert a value

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

attach a sample sheet with explanation.

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:

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

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

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