MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Writing a "+1" Formula


Posted by Shane Evans on October 09, 2000 10:03 AM

I would like to write a formula in Cell 1R to allow me to do the following...I have a number in Cell 1A that I want divided by 8. I want each consecutive cell in row 1, column B thru Q, to be divided by one more than the cell before it. So, Cell 1A would be divided by 8, Cell 1B would be divided by 8+1, Cell 1C would be divided by 9+1 (or 8+2), Cell 1D would be divided by 10+1 (or 8+3),...
Thank You


Posted by Celia on October 09, 2000 10:40 AM


Shane
Put the following formula in cell R1 and fill it across the columns as far as requred :-

=A1/(COLUMN()-11)

Celia

Posted by Celia on October 09, 2000 4:57 PM

Correction


Sorry, should be =A1/(COLUMN()-10)
That'll teach me not to count on my fingers.
Celia

Posted by Shane Evans on October 10, 2000 7:20 AM

Re: Correction

I would like to write a formula in Cell 1R to allow me to do the following...I have a number in Cell 1A that I want divided by 8. I want each consecutive cell in row 1, column B thru Q, to be divided by one more than the cell before it. So, Cell 1A would be divided by 8, Cell 1B would be divided by 8+1, Cell 1C would be divided by 9+1 (or 8+2), Cell 1D would be divided by 10+1 (or 8+3),...


Celia
Sorry to you, I phrased my question wrong. Please allow me to ask it a different way.
I need a formula in T10 that states that B10 is going to be divided by 8, D10 by 9, F10 by 10, H10 by 11, I10 by 12, K10 by 13, L10 by 14, O10 by 15, and R10 divided by 16.


Thank You,
Shane

Posted by Celia on October 10, 2000 4:52 PM

Re: Correction


Shane
Do you mean that you want just one formula in cell T10? If so, what do you want to do with the results of all those divisions? One cell can't hold 9 different results.
Celia

Posted by Shane Evans on October 11, 2000 3:21 PM

Re: Correction

Celia
What I am trying to do is average cells B10 through R10. Cell B10 is a figure that needs to be divided by 8 and all the other cells with numbers need to be divided by one more than the cell before it. The problem is that every cell isn't going to have a number-Only certain ones.
I am trying to combine statistics from last football season with this football season. The row(10) that I am working on represents a team's points scored at home. Cell B10 represents all of last season's home points combined. The "8" comes from the amount of home games that were played last season. Cells C10 through S10 represent each individual week of this football season. Since there are only going to be 8 home games this season, I am only going to be entering data in 8 of the 17 cells. I want to be able to enter the stats after the game is played and for those stats to automatically average together in cell T10. For example, Let's say it's the beginning of the season. I would want cell T10 to divide cell B10(last years stats) by 8. Then, let's say in week 2 (D10)there was a home game played. I want T10 to average B10 + D10 (Total divided by 9). The next home game will be entered in cell F10: however, it has not been played yet. I would like a formula to automatically average that figure, with the others, when I enter it in F10.
I thank you for your help and patience with this. I don't know if such a formula exists; but I'm certain that if one does, you will know it.

Shane

Posted by Celia on October 12, 2000 5:14 AM

Re: Correction


Shane
=SUM(B10:S10)/(COUNTIF(B10:S10,">0")+7)
Celia

Posted by Celia on October 12, 2000 5:24 AM

Another correction

Should be :-
=SUM(B10:S10)/(COUNTIF(B10:S10,">=0")+7)
Celia