Improve company productivity with a Business Account.Sign Up

x
?
Solved

XL Macro to add every 4th row cell to total

Posted on 2000-05-08
12
Medium Priority
?
331 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
  • 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
The Firewall Audit Checklist

Preparing for a firewall audit today is almost impossible.
AlgoSec, together with some of the largest global organizations and auditors, has created a checklist to follow when preparing for your firewall audit. Simplify risk mitigation while staying compliant all of the time!

 

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

Worried about phishing attacks?

90% of attacks start with a phish. It’s critical that IT admins and MSSPs have the right security in place to protect their end users from these phishing attacks. Check out our latest feature brief for tips and tricks to keep your employees off a hackers line!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
There's never been a better time to become a computer scientist. Employment growth in the field is expected to reach 22% overall by 2020, and if you want to get in on the action, it’s a good idea to think about at least minoring in computer science …
The goal of this video is to provide viewers with basic examples to understand how to create, access, and change arrays in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.

606 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