Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XL Macro to add every 4th row cell to total

Posted on 2000-05-08
12
Medium Priority
?
325 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 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

Technology Partners: 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

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…
Preface I don't like visual development tools that are supposed to write a program for me. Even if it is Xcode and I can use Interface Builder. Yes, it is a perfect tool and has helped me a lot, mainly, in the beginning, when my programs were small…
The goal of this video is to provide viewers with basic examples to understand and use structures in the C programming language.
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.

704 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