3 responses
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Sep 9, 2009 at 02:58 AM
Sep 9, 2009 at 02:58 AM
In the next column put the numbers 1 to 4500
In the next column put the formula =ROUNDUP(A1/8,0) where A1 refers to the cell containing 1
In the next column put the formula =ISODD(B1)
In a cell put the formula =SUM(A1:A30*C1:C30) Press CTRL + SHIFT + ENTER to enter the formula
here a1:A30 is your data and C1:C30 is TRUE/FALSE column
In the next column put the formula =ROUNDUP(A1/8,0) where A1 refers to the cell containing 1
In the next column put the formula =ISODD(B1)
In a cell put the formula =SUM(A1:A30*C1:C30) Press CTRL + SHIFT + ENTER to enter the formula
here a1:A30 is your data and C1:C30 is TRUE/FALSE column
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Sep 9, 2009 at 03:05 AM
Sep 9, 2009 at 03:05 AM
Otherwise
enter the formula
=SUM(B1:B30*ISODD(ROUNDUP(ROW(B1:B30)/8,0))) where B1:B30 is your data range
in a cell by pressing CTRL + SHIFT+ ENTER
enter the formula
=SUM(B1:B30*ISODD(ROUNDUP(ROW(B1:B30)/8,0))) where B1:B30 is your data range
in a cell by pressing CTRL + SHIFT+ ENTER
Hello, and thanks very much for trying to answer my problem.
I tried the formula out on a dummy set of data that I setup in column AC438:ac501 and randomly filled it with 0 and 1's. Starting with summing the 1's in the first eight cells and skipping the next eight cells etc., the dummy answer came to 21, however the formula produced 11. The wrong answer. I thought that fantastic formula deserved a better fate. I then decided to check if the formula was summing by skipping the FIRST eight and summing the next etc., and it was. Now how do I get it to start summing from the start. ? I tried changing the summing starting point by adding eight rows to the sum range but it made no difference. What do I need to change please ?
Thank you.
Les.
I tried the formula out on a dummy set of data that I setup in column AC438:ac501 and randomly filled it with 0 and 1's. Starting with summing the 1's in the first eight cells and skipping the next eight cells etc., the dummy answer came to 21, however the formula produced 11. The wrong answer. I thought that fantastic formula deserved a better fate. I then decided to check if the formula was summing by skipping the FIRST eight and summing the next etc., and it was. Now how do I get it to start summing from the start. ? I tried changing the summing starting point by adding eight rows to the sum range but it made no difference. What do I need to change please ?
Thank you.
Les.
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Sep 11, 2009 at 11:47 PM
Sep 11, 2009 at 11:47 PM
In the formula i have given the first eight rows 1-8 divided by 8 and get the nearest bigger whole number will give the answer as 1, an ODD number. The formula adds all values corresponding to all such odd values. So in your case the first three rows 438-440 when divided by 8 will give odd values rest will give EVEN VALUES. So to make all the first 8 rows to give ODD values, use the formula. =SUM(Your data range here*ISODD(ROUNDUP((ROW(Your Range Here also)-5)/8,0))) change the 5 in the formula to adjust the first 8 to return ODD Values.
