Thread: 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:

=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