Solved

XL Macro to add every 4th row cell to total

Posted on 2000-05-08
12
322 Views
Last Modified: 2010-04-15
A common spreadsheet problem.
How to add the value in the (nth) 4th row in a column to total row at bottom of the range. Data is uniformly formatted in this fashion.
example:

Cell - Value
A1 = 10
A2 = 3
A3 = 7
A4 = 5
A5 = 2
....
Total = 12 (Rows 1 and 5 only)

Thanks -
Bob

0
Comment
Question by:bsnare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 1

Expert Comment

by:ntdragon
ID: 2789943
i didn't get you exectlly can you be more clear
about going to a cell there are the function cell() and the func range()
0
 

Author Comment

by:bsnare
ID: 2789994
ntdragon
A worksheet contains:

GL#      Data                      Hours
-------------------------------------      
6010-32-00-0006010      Budget       16
Supervisory - Labor      Actual       24
        
6020-32-00-0006020      Budget       144
Admisistrative  Labor      Act       225              
6070-32-00-0006070      Budget       8
Training                Actual       1             
7020-32-00-0007020      Budget       0
Recruitment Labor      Actual       5             
7050-32-00-0007050      Budget       4
Bid and Proposal       Actual       0
-------------------------------------
(continues for 500 lines)

                 Total Budget:  ???

How to create a formula or macro to total only the "Budget" row hours?
Assume the "Budget" row is every 3rd row through row 500.  

Does that help?



0
 
LVL 1

Expert Comment

by:ntdragon
ID: 2790078
i"ll write it for you tomorrow 'cause i'm not at home right now

but another question where do you want the result to be stored???
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bsnare
ID: 2790082
ntdragon
A worksheet contains:

GL#      Data                      Hours
-------------------------------------      
6010-32-00-0006010      Budget       16
Supervisory - Labor      Actual       24
        
6020-32-00-0006020      Budget       144
Admisistrative  Labor      Act       225              
6070-32-00-0006070      Budget       8
Training                Actual       1             
7020-32-00-0007020      Budget       0
Recruitment Labor      Actual       5             
7050-32-00-0007050      Budget       4
Bid and Proposal       Actual       0
-------------------------------------
(continues for 500 lines)

                 Total Budget:  ???

How to create a formula or macro to total only the "Budget" row hours?
Assume the "Budget" row is every 3rd row through row 500.  

Does that help?



0
 
LVL 1

Expert Comment

by:ntdragon
ID: 2790087
btw you want to sum the budget right???
0
 

Author Comment

by:bsnare
ID: 2790094
The total would be at the bottom, however many rows down that is. Thanks -
0
 
LVL 1

Expert Comment

by:ntdragon
ID: 2798876
i still not at home but try this
i'm not sure about the ord()
i don't remember the name of the function that returns the assci code of the char

dim i
dim sum
sum=0
for i=1 to 500
sum=sum+ord(Cells(3,i))-ord("0");
next i
i=i+1
Cells(1,i)="Total"
Cells(2,i)="="
Cells(3,i)=chr(sum++ord("0"))


0
 

Author Comment

by:bsnare
ID: 2800743
Adjusted points from 50 to 200
0
 

Author Comment

by:bsnare
ID: 2800744
ntdragon

I have tried this but did get hung up on the "ord" function. I also tried Char and Asc but am not getting it. Is the ORD function adding the contents of the cell to the Sum? I guess I don't understand how that is happening. Can you clarify a bit? Thanks

Bob
0
 

Author Comment

by:bsnare
ID: 2800783
I see I screwed up here. This question should be in the Excel area rather than "C" programming. Oh well - If you can't help further it's quite understandable. I should re-ask this in the right forum. Thank you very much for your help anyway.

Bob
0
 
LVL 1

Accepted Solution

by:
ntdragon earned 200 total points
ID: 2802394
try this:
dim i
dim sum
sum=0
for i=1 to 500
sum=sum+Asc(Cells(3,i))-Asc("0");
next i
i=i+1
Cells(1,i)="Total"
Cells(2,i)="="
Cells(3,i)=chr(sum+Asc("0"))

what i mean is
i want to change the data in the cell
into a number to sum it and then back to string to write it back into the last cell




0
 

Author Comment

by:bsnare
ID: 2804621
Thank you so much -

Bob
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Line meaning 9 98
Setting nameservers after res_init fails doing res_query 2 138
Unable to start eclipse ? 17 174
C Programming - If Statement 8 84
Have you thought about creating an iPhone application (app), but didn't even know where to get started? Here's how: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Important pre-programming comments: I’ve never tri…
Examines three attack vectors, specifically, the different types of malware used in malicious attacks, web application attacks, and finally, network based attacks.  Concludes by examining the means of securing and protecting critical systems and inf…
The goal of this video is to provide viewers with basic examples to understand how to use strings and some functions related to them in the C programming language.
Video by: Grant
The goal of this video is to provide viewers with basic examples to understand and use for-loops in the C programming language.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question