Solved

XL Macro to add every 4th row cell to total

Posted on 2000-05-08
12
319 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This tutorial is posted by Aaron Wojnowski, administrator at SDKExpert.net.  To view more iPhone tutorials, visit www.sdkexpert.net. This is a very simple tutorial on finding the user's current location easily. In this tutorial, you will learn ho…
This is a short and sweet, but (hopefully) to the point article. There seems to be some fundamental misunderstanding about the function prototype for the "main" function in C and C++, more specifically what type this function should return. I see so…
The goal of this video is to provide viewers with basic examples to understand and use pointers in the C programming language.
The goal of this video is to provide viewers with basic examples to understand recursion in the C programming language.

821 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