Sum to Defined level

Programming and Web Development Forums - MS EXCEL - Excel Forums for Business Finance, Analysis and Application Development

Skip to content

Sum to Defined level

MS EXCEL
Post a reply

    Next

  • 1. Template Excel Worksheet
    Hi! I'm trying to set up a template worksheet for data being exported from a proprietary program that runs on a Progress database. The software has the functionality, but I don't even know how to get started! Is there somewhere that someone could point me where I could get that know-how? Or even just a couple of pointers so I could figure it out myself over a couple of hours. Right now, the program creates its own columns of data and I go in and copy and paste the formulas and formats. I want it to dump those columns into an EXISTING template.
  • 2. Can it be done?
    Since I have had some success in getting answers on here, my final few problems should be easy meat for someone surely? I have columns from A - AB which stretch across the page farther than the eye can see and which it is getting to be a pain scrolling across and filling down as new data is entered in columns A - Q, the remaining columns are only used for calculation but still have to be filled down. I know I can hide columns R - AB by moving them across to cover each other, but is there any way of getting them to fill down automatically once Column Q is filled? Finally, thinking ahead - because I want to hide them. Is there a method of asking an IF question in column AB that will change the colour of the entry in Column Q to RED if a condition is met? Many Thanks in Advance
  • 3. Student Attendance Record Template
    I like the template, but I would like to know how to get the totals to appear on the document.
  • 4. HOW DO I HAVE TWO ROWS OF SHEET TABS?
    I have a worksheet which currently has around 20 sheet tabs at the bottom, with more being added in the future. Is there any way to have more than one row of tabs, so that I can see all the tabs at once without having to scroll back and forwards?

Sum to Defined level

Postby TmF0ZSBXYWxzaA » Tue, 15 Mar 2005 09:45:07 GMT 

I was wondering how I could add a column of numbers up to a defined level 
(250000.00) then add the remaining numbers in the column?
Top

Re: Sum to Defined level

Postby Max » Tue, 15 Mar 2005 10:33:20 GMT 

One way to try ..

Assume the col of numbers is in col A, A1 down

Put in B1: =SUM($A$1:A1)
Copy B1 down

(Col B returns a cumulative sum)

Put in C1: =SUMIF(B:B,"<=250000",A:A)
Put in D1: =SUM(A:A)-C1

C1 returns the cumulative sum of col A
up to the defined level of 250000

D1 returns the sum of the remaining numbers in col A

You could also have C1 point to a cell instead
for the defined level, say to cell E1:

In C1: =SUMIF(B:B,"<="&E1,A:A)
where E1 will contain, say: 250000
Top

Re: Sum to Defined level

Postby CLR » Tue, 15 Mar 2005 10:54:35 GMT 

=IF(SUM(A:A)>250000,SUM(A:A)-250000,SUM(A:A))

Vaya con Dios,
Chuck, CABGx3
Top

Similar Threads:

1.Pivot Table - report product that have sales above defined level

2.Define User Access Levels to Workbooks 

Scenario:

I have a Workbook which works as an index page of a system that I have
designed for my company based on very little VBA knowledge. The page is
a series of buttons acting as hyperlinks to open the other
documents..... in all 15 Workbooks divided into Sales, Accounts,
Warehouse management etc......

Question 1. Can I create a User Name & Password at a start page that
provides access to my index page?

Question 2. If so Can User Names and Passwords be created to allow
different access levels i.e  a Warehouse login can not see Accounts
links and an Accounts login cannot see warehouse links etc...?

In this instance the password for an Accounts employee would need to
give access to 4 different workbooks - can this be set to automatically
"unlock" them so that the password prompt doesn't come up each time a
workbook is opened

Question 3. I would also need to create a Master Password for Admin.

Question 4. does this make sense?

Question 5. Can anyone help?

3.What defines an advanced level of Excel? 

I was wondering if somebody could help me with this.  I was looking at 
applying for an office assitant job and it said I needed an advanced level 
of Excel.  I have been using Excel for some time now, although I recently 
decided to do a short course starting from the basics to help identify what 
I may be missing in my knowledge.

We didn't delve into complex formulars but covered all our basics and gave 
our selves the background knowledge to explor further.  I am all over the 
place, such as inputing data, retreiving data from sources such as SQL 
server, text files and so on.  Formatting spread sheets and creating tables. 
As well a bunch of other stuff.

Not sure if this puts me in the Intermediate level or still beginner.

Scott M

4.sum sub levels. 

Hi there,

My name is Jerome. I would appreciate your help with the following problem. 
I have an excel sheet whcih contains data displayed in various levels. There 
are 11 levels in total. There are over 5000 rows. However each level has 
different categories. I have to add the values of level 2 in level 1 and all 
the values of level 3 into 2 and so forth. See tteh format below.

You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES, 
PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS)

I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ASS, 
TRANSFERS RECEIVED,  FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND, RENT 
ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS)

LEVEL	CODE	AMOUNT	1	2
1	96623		PAYMENTS	
2	982623			PAYMENTS
2	988623			TRANSFERS AND SUBSIDIES
2	996623			PAYMENTS FOR CAPITAL ASSETS
1	6318623		DIRECT EXCHEQUER PAYMENTS	
2	6319623			DIRECT EXCHEQUER PAYMENTS
1	1623		RECEIPTS	
2	2623			TAX RECEIPTS
2	3623			SALES GOODS & SERV NON CAP ASS
2	40623			TRANSFERS RECEIVED:
2	65623			FINES, PENALTIES AND FORFEITS
2	72623			INTEREST, DIVIDEND, RENT ON LAND
2	102623			SALES OF CAPITAL ASSETS

I have to do this for the entire worksheet. Could you please advise me how I 
should go about this.

Thank you very much!:O)

Best regards,

Jerome

Post a reply

Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 72 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group