Using IF Statement To Carry Over Grand Totals Based On Base Number
Apr 8, 2009
I have a number of Grand Totals that equal to Hours of Work in a day ( Based on Demand from Customer Orders)
I only have 95 ( this will be a number in a cell that I want to be able to change if needed) work hours available to me each working day.
I want each day to attempt to fill in up to 95 hours , anything more and it will push the remaining balance forward into the other cells.
IE here is what I have for the next 5 days for Totals
211.3798120.304870.0099110.734375.041828.1292
Under the 211 I want it to change to 95 and then carry over the balance to the cell under 120 , I then want that cell to change to 95 and carry over its balance to the next cell and so on down the line. I will always have 22 Working Days I want to work with. So the last day may or may not have a greater then 95 total.
The 95 part I want to be able to change that to whatever number I think I will have available to me and it will adjust accordingly through the line.
View 10 Replies
ADVERTISEMENT
Feb 11, 2009
I created a time sheet for employees which calculates the total wages each week. I copy the worksheet to create a new time sheet for each week. I want all subsequent time sheets (worksheets) after the first one to calculate the total wages to date by adding the total wages of the current sheet to the total on the previous sheet. If for example I am on Week 2, I know how to reference the wages cell from the previous week by using something like ='Week (1)'!M28, but when I copy the Week 2 sheet to create Week 3, the new sheet still references Week 1. I don't want to manually change the sheet number each week. How can I make it reference the previous week automatically?
View 9 Replies
View Related
Jul 26, 2009
I ma going to have a couple of tricky (to me at least) table/range look ups, so let me start with this one:
.......a........b...................c.....................d....................e
1............Dive 1......................................School Grand Totals
2..team.....diver nr......... points...............Boys...............girls.
3 md b.......1..................3.....................md b...8..........md g...4
4 md b.......2..................3.....................jb b....7
3 jb b........3..................3................
6 md g,......4..................3
7
8..............Dive 2
9..team.....diver nr......... points...
10 md b.....1..................1...........
11 md b......2.................1...........
12 jb b......3.................4
13 md g......4................1
Notes about above. The naming is like this. "md b" is "martin county boys". "md g" would be "martin county girls". "jb b" is "jensen beach boys".
I need to add up each school grand totals for all the dives, and break them down to boy or girl for each school. this score will eventually go into another total which totals 11 swimming events by boys or girls.
So, in essense, thre are going to be several totals for the meet:
1 Diving (6 dives) by School by Boy (note: only showing 2 dives here)
2 Diving (6 dives) by School by Girl
3 Swimming, (11 events) by School by Girls
4 Swimming, (11 events) by School by Girls
5 Grand total by school (boys and girls scores combined)
Texaslynn (I believe that was her handle) helped me previously with totaling a grand total. However, now I discovered I need the total by boys and girls and then the combined (boys and girls) school total.
View 9 Replies
View Related
Nov 8, 2006
I need to know how to calculate totals from a spreadsheet in conjunction with an IF statement.
I have a columns A & B below, i would like to calculate the numbers in coulmn B only if they match a specific criteria in column A.
For example: I want calculate the total of the numbers in coulmn 'B' only if column A is a '1'.
AB
24
33
12
21
35
13
22
View 10 Replies
View Related
Feb 10, 2014
how to write a formula or function to add up adjacent totals in column B that match the "name" in column A?
For example:
Mary's name is entered multiple times in column A and her earnings are recorded in column B adjacent to her name. How do I add up her totals only with a IF statement?
View 2 Replies
View Related
May 20, 2009
I have Column A with chronological dates. Some of them will be the same. If the dates are the same then the lowest row of those dates would need to add all the corresponding values in column D and print the total to column E
eg.
A1 through A3 = 2/12/2009
D1 through D3 would need to be added
E3 would display total for =d1:d3
it would then do this for each of the dates that match in Column A
View 4 Replies
View Related
May 26, 2006
I have 4 and 5 digit numbers. For the 4 digit numbers, I want to be able to distinguish between the numbers by the last digit. For the 5 digit numbers, I want to be able to distinguish between the numbers by the last two digits.
Example: For 4 digit numbers, I would like to do something with all numbers ending in 1, 2, 3, 4, 5, 6, 7, and 8. For 5 digit numbers, I would like to do something with numbers ending in 10, 11, 12, 20, 21, etc.
The first step in being able to do this, I guess is distiguishing between 4 and 5 digit numbers, which I know can be done by the number lenght. However, the second part of looking at the last digit or the two last digits I don't know how to do.
View 4 Replies
View Related
Nov 20, 2006
I have a macro that has following line
Range("C38'").Select
I want to dynamically change the C38 to may be C37 depending on a value in a cell i.e. F1
so if the value in cell F1 is 31 then I want this statement to look like
Range("C31'").Select
View 3 Replies
View Related
Oct 3, 2009
How can I add the number of remarks to the number classes based on their row?
I have a TALLY SHEET which auto computes the number of occurances of each classes
and remarks…can someone help me how to add the class and remarks? In this example
you can see that CLASS A occurred 3x ,YES remarks occurred 2x and NO remarks occurred 1x for A class…
how can I add the occurances of YES and NO remarks to A class?
This should be the output…Remarks are being added according to the class they belong
ABEYESNONot Applicable
643242
View 13 Replies
View Related
Oct 7, 2009
how to count the number of occurrences base on a criteria? My sample file contains a Tally Sheet and a template sheet...how can i count the number of occurrences of Yes and No per class? Say for the A class, how can i count the yes or no?
View 2 Replies
View Related
Jun 11, 2008
totaly new to Excel (just using two weeks) as a database
I need to number a colum, 1 to what ever, so that I can use that colum to re-sort the data base back into original order.
View 9 Replies
View Related
Mar 5, 2012
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
View 3 Replies
View Related
Dec 26, 2008
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
View 5 Replies
View Related
Jun 12, 2007
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).
View 6 Replies
View Related
Jan 8, 2014
Excel Forum.jpg
So I want the SUM of the total from a certain range of dates to go into the Week Columns on the right. I'm gonna manually select the date ranges for each Week but how can I set up a formula to Sum the Totals at the right of the table based on the Date Column.
View 7 Replies
View Related
Jan 9, 2014
Based on the photo below i am looking to sum and multiple totals based on information presented.
I am looking to have the Total items calculated based on either the size run value(which in this case is 111, or 121, or 123,or 222 etc, all based on clothing sizes 1-s, 1-m,1-l) or calculated based on the Total from the sum of all the sizes (xs,s,m,l) which in this case is 9. and if that was not enough, then have the Total Items multiple based on the No of Packs, which in this case is 6.
In this example i have managed to get the formula to multiple 1 size run (111 = 3) multiplied by the number of packs (6) to get 18, (Size Run, is a drop down list from a reference page
I guess i need to know if there is an AND or OR function that can be combined with the SUM or SUMIF function.
Excel formula.jpg
View 2 Replies
View Related
Jun 3, 2006
I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.
View 14 Replies
View Related
Sep 17, 2007
Spreadsheet consists of the following:
Worksheet 1
B10 : B49 = Names
AO10 : AO49 = Amount
Worksheet 2
I want it list the Names only from highest to the lowest from worksheet 1. If two Names have the same total then I want it in Alphabetical order towards who will be listed first.
Example:
Worksheet 1
B10 Sam AO10 10
B11 John AO11 14
B12 george AO12 16
B13 Steve AO13 9
B14 Carlos AO14 9
Worksheet 2
george
John
Sam
Carlos
Steve
View 9 Replies
View Related
May 28, 2014
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Book10.xlsx‎
View 4 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Jan 28, 2010
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
View 9 Replies
View Related
Jun 22, 2009
I am using Microsoft Excel 2003. My question is about calculating time. 1 hour + 1 hour and fifteen minutes would equal two hours and fifteen minutes. Using Microsoft Excel 2003, let's say I am using cells A1, A2, A3 and A4.
A1 will be 1:00 for 1 hour
A2 will be 1:15 for 1 hour and fifteen minutes
A3 will be my total for adding cells A1 and A2 and the answer will be 2:15 for two hours and fifteen minutes.
My specific questions is: Would it be possible for me to have the fifteen minutes (0:15) from the two hours and fifteen minutes (2:15) automatically carry over to cell A4 or cell A4 of another worksheet without having to type in 0:15 or having 2:15 appearing in cell A4?
View 3 Replies
View Related
Nov 3, 2009
I have 2 subs routines in a mod. I have declared the two variables at the top of the sub. However when I call the second sub the variable's are not passed along.
Here is an example script. All in one mod. I have taken out the junk in between to help edit the problem.
I'm looking to pass the same bnumber and dnumber to the secound sub.
View 2 Replies
View Related
Apr 30, 2012
below fig 1 is a part of a worksheet we are using for Stock. The idea is to track stock write downs on a yearly basis. Where I am struggling is a formula to carry the stock balance and value forward if there has been no movement in a given year.
The enteries in green (fig1) colums "Stk Forw" & "Car Fwr" represent how I would like the info to look.
Is there a formula that could look for the last entry in "Stk In/Out" if the column "YTD to date" is empty and carry that value forward to the next row.
I Would also need a formula to sum the Total & Car Fwr colums as one total.
Fig 2 Shows the formula I am currenlty using.
View 1 Replies
View Related
Jan 2, 2007
I have a macro which is shown below.
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("I1").Select
Application.CutCopyMode = False
Selection.Copy
What im trying to do is to be able to run the same macro but start on the last cell that I had selected and work on down the sheet until the end. What im also trying to do is to be able to post into next avail line on sheet 1 so it dosent overwrite.
View 9 Replies
View Related
Apr 18, 2007
Attached is a sample of excel file,
its a simple spreadsheet that suppose to track a usage of items that were taken out of inventory,
but at the same it needs to be printer friendly (that's what's causing all the problems),
it got messy when I had to carry over from previous row,
I had to manually enter under "Quantity ch." in order to have correct amount under "Left" column, (see row #4)
I think I can eliminate whole "Carried over" column if I create a formula that will enter data into B4 only if J3 has a number, I also need to copy the same formula's down each column,
View 9 Replies
View Related
Nov 17, 2008
I want to input pick 3 (3 numbers) into a cell and with the assigned SUM to that cell to add +123 to the 3 numbers I input, but how do I get the answer not to carry over?
For pick 3 games, the numbers are; 0,1,2,3,4,5,6,7,8,9 (ten total).
When I add +123 to 987 I get 1110. I don't want that. I would like it to show 010 instead, lotto numbers no carry over.
View 10 Replies
View Related
Apr 16, 2014
I'm looking for some ideas on how I can improve my invoice I have on excel,currently I have a spread sheet made to look like a standard invoice that prints on one page.
the problem is the more lines the invoice has the smaller the righting gets as it only prints one page.
how can I get my invoice to look nice, still automatically add up all the lines but go into two pages?
I've tried splitting the invoice but it looks professional.
View 2 Replies
View Related
May 23, 2014
I have some values ​​in 1st sheet and 2nd sheet and I needed to carry the values ​​to another worksheet making a list
Like this:
In 1st worksheet I have
A B
a 353125
b 643643
c 532
In 2st worksheet I have
A B
x 754
y 64363
z 23626
I want in the 3rd worksheet to return all values ​​of the other worksheets
Like this:
A B
a 353125
b 643643
c 532
x 754
y 64363
z 23626
how do I do this?
View 1 Replies
View Related
Jul 16, 2014
I have a sheet which is just a list of product codes and a sum of every order placed for each code. i.e.
Sheet2 Â AB1Prod codeOrdered2123100345650478960
then I have another sheet which is the orders placed by our customers. These orders are to go out in 6 periods each of 2 weeks.
For various reasons the amount ordered each time won't match the periods.
The sheet beforehand will look like
Sheet1 Â ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC212327Â 20Â 35Â 20Â 12Â 11Â 345633Â 40Â 50Â 15Â 25Â 11Â 478915Â 20Â 20Â 20Â 20Â 10Â
What I need to do is look up the quantity ordered and then allocate the ordered quantity to the periods carrying over the remainder to the next period and so with the table above the result would be
Sheet1 Â ABCDEFGHIJKLM1Prod codeP1 REQALLOCP2 REQALLOCP3 REQALLOCP4 REQALLOCP5 REQALLOCP6 REQALLOC2123272720203535201812Â 11Â 34563333401750Â 15Â 25Â 11Â 478915152020202020520Â 10Â
Both sheets are sorted by the product code ascending and it doesn't matter if it is a formula or VBA based solution as I am already using both.
The number of product codes is currently just over 400 but will grow to about 550 by the end of period 6.
View 5 Replies
View Related
