make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
I have a workbook with calculations for a sale less the assorted fees and at the end giving the final amount from a sale.
I have noticed that some of the rows are not giving the correct amount in them.
In other words the addition of some columns in that row are not adding up correctly. It is only off by 1 cent (either over or under), but I can't figure out why.
I have the feeling that I am going to want to kick myself when someone explains this to me (I just know that I know the answer but for the life of me I can't right now).
The following formula seems to be incorrect. I typed in the times and dates to get the difference in hours, but my times are in 5 minute blocks, so the answer is given doesnt add up
Start DateFinsh date Hours15/11/2011 13:5017/11/2011 2:2536.58
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
I have this formula in Column L. The calculations are working fine and I close the file. After I email the workbook from one computer to another and then resave it, every row in Column L that has the formula turns to #NAME?
Do you know why this is happening?
I am trying to make a calculation using variable text.
My outcome cell is D3 and my two variable cells are C3 and F3. In C3 I want to be able to insert a staff members name, of which we have 6 different members of staff. In F3 i need to insert the week number which runs 1 through 6. The outcome is the individuals place on the week roster which needs to be a number between 1-6 inclusive. SO basically if F3=1 & D3=J.SMITH then I need D3 to equal 1 keepng in mind that if F3 & D3 have 6 possible variables.
I wanted to have the weeks of the month down one column = 52 week.
down the next column I have different amounts of money in that week.
some months have 4 weeks and other have 5. I wanted a program to say:
If you see a month "x" look at the next column and take that amount. Then on the next row you have month "x" again (week 2) go to the next column and take that amount and add it to week one. And so on until all 4/5 week are added to give on result.
Then the same for the next month...
month amount/week amount/month
05-Mar 0
12-Mar 70
19-Mar 210
26-Mar 350 1050
02-Apr 420
09-Apr 455......
I am hoping someone with excel experience can be of help to me with an unusual request for excel.
Assume cell A1 = 2, B1 = 3 and i wish the sum of this (5) to appear in cell C1. Very straight forward so far, however i wish the result to appear in C1 when i left click on a cell other than C1, say for example D7.
I can't use any macros for this.
I need to C8 - C19 only to add up jobs won by andrew (in current orders). It needs to be month specific. what i mean by that is I need the formula to do what its doing now (adding up the jobs by and putting the totals into the according cell depending on what month they were won.
View 2 Replies View RelatedI've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)
View 2 Replies View RelatedI have multiple ranges in a spread sheet. I am trying to write a formula that will go out to each range in succession and look for a part number, upon finding return a quantity and them move on to the next range duplicating the above process. The formula should tally the grand total of all numbers found. I have it working except that not all of my items are in all ranges. If the item that I am searching for is in all ranges my formula works but if there is one or more of the ranges that doesn't have that particular value it returns an #n/a instead of totalling those that do have it. If I use a true instead of false in my [range_lookup] I get an incorrect answer. My formula for a given cell is listed below. This is with the true argument which does not work....
View 9 Replies View RelatedI am a new excel user. I a trying to write a certain formula but am having trouble. I want to write the formula to add a column of numbers, say H-10 through H-15. Each cell will have a number in it, but I want only to add the cells if the cell precedding it in the G-10 through G-15 Collumn is blank. For example if cells G-12 and G-14 have an "X" in them, then I do not want Cells H-12 and H-14 to be added. I only want the formula to add cells H-10,H-11,H-13, and H-15. I used just 6 cells for example, the column of cells to be added will be a lot longer.
View 3 Replies View RelatedIdenttime altlatlon
a3340.3320004551
a3360.3325004450
a7880.3350004425
a5210.3310004910
a8520.42000453
a9560.51002030
a8410.5580002063
a9650.680002465
I need a formula that recognizes identical times then takes the difference between the altitude, lat, long of the two identical times.
So ideally the calculation that would be performed.
Ident (a334)-(a336) 2000-2500 45-44 51-50
Ident (a334)-(a788) 2000-5000 45-44 51-25
Ident (a334)-(a521) 2000-1000 ''''''' ''''''''''
Ident (a336)-(a788) 2500-5000 '''''' '' ''''
Ident (a336)-(a521) 2500-1000
Ident (a788)-(a521) 5000-1000
I created a pivot table to try to calculate the difference but it cannot do it an a algorithmic type fashion like this. Then eventually I need to conditionally format it to if all three meet a certain criteria that it will put it in a separate chart only if all three conditions are met.
So if Ident (a334)-(a336) 500 1 1 (ex. of all three criteria being met [actual parameters not important but these values will be flagged in my conditional formatting])
This needs to then be put in a separate chart with all other data that meets all three criteria.
So the steps I need to perform this function:
1. Does Ident "x" equal Ident "y" time?
Yes: Take difference between all three parameters. Do all three meet Criteria?
Yes: Put all calculated info in separate chart if they meet all three criteria. Does Ident "x" time equal Ident "z" time?
Yes: Take difference between all three parameters. Do all three meet Criteria?
No: Generic Algorithim.xlsxEnd
Yes: Put all calculated info in separate chart if they meet all three criteria. Does Ident "x" time equal Ident "aa" time?
No: End
No: End
No: Move on to does Ident "y" time equal Ident "z" time?
[Rinse, Wash, Repeat]
I obviously know less about functions than I thought I did. I've got the attached spreadsheet set up except getting totals at the bottom. The production total L44, would be column A multiplied by the quantity entered in columns L and summed. Same for Total SF, square footage in column B times quantity in L and summed at the bottom. This would continue daily, needing sums under each column.
View 4 Replies View RelatedHave an excel table with following data:
- ID
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)
201688194000bottles
20168819200crates
2016883812000bottles
20168838600crates
201688396400bottles
20168839320crates
201688809000bottles
20168880600bottles
20168880480crates...................
I need to write a macro which will do this operation for each ID:
(bottles/20)-crates = x
and if "x" is not 0 then write down the value of "x".
There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)
I wish to sum specific rows in a spreadsheet as a Grand Total. I eventually end up with a string variable called "GrandTot" that holds "=C3+C13+C25+C31+C39+C50+C53"
This formula is correct, but when I insert it into the spreadsheet it totals zero. Only when I manually write the formula does it recognize the cells it supposed to add. I am very confused as to why this is happening.
On a possibly related note, when I highlight a column in the spreadsheet, the count is including blank cells.
I have a macro that basically captures all of the errors in my spreadsheet and summarizes them under 3 columns (Value in Cell, Sheet, Cell) so that I can quickly identify the error message as well as it's location. I wanted to take it a set further and add a hyperlink for each error so that the user can quickly go to the error location to investigate. I really don't care where the hyperlink is located as far as which 3 columns.
Range("G2").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
curRow = ActiveCell.Row + 3
[Code] ........
Lets say I have a #2 in cell A1 and #4 in cell B1 and would like an output in C1 as "plus 2".
Right now I have =if(a1>b1,b1-a1,???) what can I put in the [value_if_false] to return "plus 2" or do I have to use another formula?
I have this in cell F5
CM26 (85) CM16 (39) CM25 (12)
I need to add the numbers in brackets up and put the total in G5.
I AM HAVING DATA OF 210 BRANCHES OF DIFFERENT ITEMS. EACH BRANCH HAS AROUND 100 TO 150 ITEMS. I WANT TO ADD THE VALUES OF EACH BRANCH AND I HAVE TO GET THE GRAND TOTAL VALUE IN A SINGLE SHEET. SUPPOSE IF ADD E10+E210+E350+E470 LIKE THIS AFTER SOME pLUSES I WILL GET THE FORMULA RANGE IS OVER. IS THERE ANY METHOD OF ADDING 210 BRANCHES ITEMS
View 9 Replies View RelatedI am looking for a method to count the number of amounts in an addition.
For example, let's assume that A1 contains 354.92+29+84.7+45 That addition contains a total of four different amounts that have been added So, I am looking for a formula that will display the total number of amounts (4) in a different cell.
Another example:
A1 -> 75+9+237
A2 should show -> 3
I have Names in column A, Data in Column B. Example
A1 John B1 1000 C1 5:32:05
A2 Jim B2 500 C2 5:56:55
A3 John B3 600 C3 6:45:65
A4 Bill B4 300 C3 7:21:05
In another column I have the names of all the possible people that I will need data from and next to them I will need a formula to tabulate all their totals from column B and then another formula that will skip B and total column C's total.. I have a formula that I used from awhile ago when I needed to offset the data but I can't figure out how to just take the data to the right of it and then another formula to skip column B. Here is my old formula =SUMPRODUCT(($A$1:$A$291=G14)+0,OFFSET($B$1:$B$291,1,0)+0)
I imagine this is a simple conditional SUMIF function. I'd like a cell to add values in e.g. column "d" when that row meets certain criterion in column "a".
In other words, I have a column that has times recorded in minutes, and another that says a person's name which correlates with the times. I'd like a cell on another sheet to give a total sum of minutes for each person.
Ideally, part of the function would translate the minute count into hours/minutes, but I think I can figure out how to do that by changing the format in the cell...
I would like to know the calculator formula for addition via columns.
Eg 1. If i were to place 135 into Column A ;
12.95 into Column C ;
i would need to yield a result of 147.95
Eg 2. Place 189 into Column A ;
12.95 into Column C
i would need to yield result of 201.95 and so on. in the attachment is the sample file.
Included is an example of a spreadsheet I am working on. There are multiple choices within several different drop-down menu's. As of right now I have the 1st menu as the stage of completion of a car. Within the next few menu's are options.
If welded chassis is chosen, none of these options are included. However if roller or turn-key are chosen then some of these options are included. But then there are also upgrades to these parts that are included as well. Is there a way to make 1 option included when a roller is chosen, but then if you want the 2nd option in the menu, you click on it and it automatically updates the price next to it, therefore subtracting the cost of option 1 from the cost of option 2?
Adding date and time. The code is listed below.
I have watched the code while running. The initial date FDateS and FDate(t) value is m/dd/yyyy (9/16/2010) and no time. FDateS has different amounts of time added to it. The issue I have encountered is that once it passes midnight, the date does not change but the time reflects the change to 00:00:00.
Public FDate(1000) As Date
Public FTime(1000) As Date
FDate(t) = CDate(Mid(FindData("sm_Sys_BatchOpenTime"), 1, 10))
For H1 = 1 To n
FDateS = FDate(H1)
For r =1 to 50
FDateS = CDate(FDateS) + CDate(FTime(r)-Ftime(r-1))
Next
Next
I have two columns with numbers in ranging as follows:
2
2.5
3
3.5
4
4.5
5
On the spreadsheet it looks like this:
4.5
5
4
4.5
3.5
3.5
3.5
4
3.5
3.5
3
3
3.5
3.5
What I need to show in the next column is a True where these two figures add up to 8 or more. However, the minimum in each column needs to be 4 and 4 i.e. if one column has 3.5 and the next 5 then return a value of False.
I have some great code that HalfAce provided a while back that I think will fit a project I am working on, but I can't see how to modify it to fit this one. I need to have it look at a location and provider and find the most "common" date. Then for that criteria fill in the lines with no dates with that "common" date. Here is the code that I need to modify for this
Sub FillInTheBlanks()
Dim LstRw As Long, _
DescRng As Range, _
AccntRng As Range, _
Desc As Range, _
Accnt As Range
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
Set AccntRng = Range(Cells(2, "B"), Cells(LstRw, "B"))
Set DescRng = Range(Cells(2, "I"), Cells(LstRw, "I"))
I behind developed to a time a function of Addition with an only conditional criterion.
I would like to extend at least for three criteria, this function I function accurately as the function SUMPRODUCT alone that done in VBA.
Function VlookupAllSum(name As String, IntervalSearches As Range, IntervalReturn As Range) As Variant ' as integer para valores até 32.767
Dim Valor, Nome
Dim lin, col As Integer
Dim Total
Application.Volatile
lin = 1
col = lin
For Each Nome In IntervalSearches
If Nome = name Then
Valor = IntervalReturn(lin, col)................
see the attached sheet. It already has some example....I need the result of the addition in the cells of column F, at the side Say column G, in the coressponding row. e.g for cell F9, I need the result in G9, and so on. For testing, step 1. Select M+R in Col "TOI", enter some value in the pop up. step 2. Again select M+R in "TOI", enter some value in the pop up. the Col F will have some additions (e.g 1+2), for which I need the result in the corresponding next column. i.e col G.
View 4 Replies View RelatedI am working on a spreadsheet that matches each cell in Column B (text) with the data (text) in a constant cell; if there is a match, the data that corresponds to the data in Column B (text) will average (Column G, number) using a CSA formula, for example: =AVERAGE(IF($B$3:$B$106=A$110,$G$3:$G$106))
Now the formula above works well, only I have to update the spreadsheet, so when I add new rows the $B$3:$B$106 and $G$3:$G$106 portions are useless.
Trying to use the INDIRECT function that many people successfully use in this forum, produces a #VALUE error,
=AVERAGE(IF(INDIRECT("$B$3:B"&ROW()-4)=A$111,(INDIRECT("$G$3:G"&ROW()-4))))