# Function to count number of rows and insert a value

1. ## Function to count number of rows and insert a value

Hi All

Hoping you can help-I am looking or a function (row or countif I presume) that will count the number of cells/rows and insert a value after a certain count.

I'm sure there is a fairly simple formula, but can't seem to get it

thanks

Ryan

2. ## Re: Function to count number of rows and insert a value

Can you elaborate? Not clear what exactly you need... are you counting based on a criteria?

3. ## Re: Function to count number of rows and insert a value

attach a sample sheet with explanation.

4. ## Re: 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?

5. ## Re: Function to count number of rows and insert a 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

6. ## Re: Function to count number of rows and insert a value

Try, In C3:

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

copied down

7. ## Re: Function to count number of rows and insert a value

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

8. ## Re: Function to count number of rows and insert a value

``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...

9. ## Re: Function to count number of rows and insert a value

Originally Posted by NBVC
``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

10. ## Re: Function to count number of rows and insert a value

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

11. ## Re: Function to count number of rows and insert a value

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

12. ## Re: Function to count number of rows and insert a value

If you change formula in C3 slightly it will add an extra Dose if needed...

``Please Login or Register  to view this content.``
and when I use Dosage 400 with Half-Life 16 and Length=15, I get the same results as the other sheet in the other thread...

I am not a chemist or biologist.. so not sure how it all works... so don't know if tis fixes it.

13. ## Re: Function to count number of rows and insert a value

That seems to do the trick-not sure what was wrong before. Thanks appreciate the help.

14. ## Re: Function to count number of rows and insert a value

My previous formula wasn't adding that last dosage it was going up to 1 less .. so I changed the first part, the Countif() to say <= value in G3, so that it would list the correct number of dosages....

15. ## Re: Function to count number of rows and insert a value

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

16. ## Re: Function to count number of rows and insert a value

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....

17. ## Re: 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....

Thanks

Ryan

18. ## Re: Function to count number of rows and insert a value

Does the attached work for your tests- let me know either way.

19. ## Re: Function to count number of rows and insert a value

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

20. ## Re: Function to count number of rows and insert a value

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

21. ## Re: Function to count number of rows and insert a value

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...

22. ## Re: Function to count number of rows and insert a value

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

23. ## Re: Function to count number of rows and insert a value

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)

24. ## Re: Function to count number of rows and insert a value

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.

25. ## Re: Function to count number of rows and insert a value

I am off right now... but as far as I can see D2 has to have a value in it... for the addition to work... unless you use SUM() instead so it will ignore blanks...

26. ## Re: Function to count number of rows and insert a value

I think I got it working!

See attached and let me know how it goes.

27. ## Re: Function to count number of rows and insert a value

I think I got it working!

See attached and let me know how it goes.

Just one suggestion... that you change the INDIRECT() fuction to find End Range cell address to INDEX()..... the INDIRECT() function is volatile and will re-calculate everytime you change anything in the workbook (even if not related to the formula) and even when you close workbook...

Formula in D2:

``Please Login or Register  to view this content.``
copied down... would give same results and not be volatile.

28. ## Re: Function to count number of rows and insert a value

Hi NBVC- thanks for the tip re INDEX function.

I'm keen to get familiar with the index and index/match functions as they seem very useful for working with lists so will play with your modification.

I'm finding this site to be a fabulous resource for extending your Excel skills. It's nice to be able to give a little in return for the volume of tips/ guidance/ suggestions received back :-)

29. ## Re: Function to count number of rows and insert a value

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1