Shortening Summation Formula?
Jan 10, 2014
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
View 4 Replies
ADVERTISEMENT
Sep 25, 2009
I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.
=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5
From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula
*Note*
'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.
View 2 Replies
View Related
Jun 3, 2009
I'm trying to enter the summation formula into three columns that vary in placement based on the value of num_objs. Basically, the number of grey columns in each of the three sets will equal num_objs. I want the user to be able to enter values into the grey columns in the middle section and the values of the two rightmost white columns (with '0's in them currently) will change accordingly.
My current code is below. However, I keep getting a compile error saying "Expected: end of statement". I've been searching for a solution online for a couple days now, and I don't see how my code is different than what I've been seeing of other people using variables in their formulas.
View 2 Replies
View Related
Mar 3, 2009
Need to solve the formula in the file. Is it possible to solve this in just one move? in that case.
View 2 Replies
View Related
Aug 7, 2013
I have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
View 9 Replies
View Related
Jan 8, 2009
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View 2 Replies
View Related
May 6, 2008
I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).
View 2 Replies
View Related
Jun 1, 2007
I have a very long column (almost 3000 cells) of URLs. I would like to replace them in the second column with shortened versions, basically cut them at the fourth slash.
how can I used functions in Excel to do it?
View 10 Replies
View Related
Apr 2, 2008
I'm using this function to make a short description of a product from a long description.
=MID(K2,1,200)&"…"
So it's taking the first 200 characters of cell K2 and adding a ... to it.
Whats happening is it is splitting words and ending at character 200 instead of the end of the closest word to character 200.
Is there a way to alter this formula to break at the end of a word close to character 200?
Or is there a better way to do this?
View 9 Replies
View Related
Mar 20, 2008
How would I make the following code shorter/ more efficient? The columns don't vary much - either B, G or L. The rows vary a bit more....
View 9 Replies
View Related
Feb 9, 2014
I have a letter and numbers in the A column. example: (A001_C108_1231Xb)
This kind of numbers go from Column A1 : A1800.
I need to print them out, therefore I would like to fill every page which has 50 rows and 3 columns : A, D and G to write in the data.
I tried to do it manually, which is quite some work.
View 5 Replies
View Related
Jul 15, 2012
shortening the below recorded macro?
Code:
Sub Macro1()
'
' Macro1 Macro
[Code].....
View 3 Replies
View Related
Apr 18, 2008
weekly i have a report that is generated that is over 5000 lines and 4 columns wide.
I currently copy and paste one page column to make it 16 columns across the page. Is there a function in excel to do this?
View 9 Replies
View Related
Jan 16, 2007
How would I shorten this code using a loop and arrays? It is working correctly but needs to be shortened down.
Sub cr()
Dim j
Cells(1, 1).Interior.Color = RGB(250, 155, 100)
j = 0.09
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.094
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.043
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.05
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.034
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.032.........................................
View 4 Replies
View Related
Mar 14, 2007
I need a macro that evaluates the range H66:GI66.
In that range the following variables could exist:
A3, A4, A5, B3, B4, B5, C3, C4, C5, D3, D4...all the way to I5 for a total of 27 different combinations.........
View 9 Replies
View Related
Jan 17, 2007
I have put together some code which works fine but is very clumsy and, when it comes to expanding it in the very near future, will look really bad.
rn = Worksheets("Test"). Range("C1").End(xlDown).Offset(1, 0).Row
Worksheets("Test").Range("C" & rn) = Odate 'a variable already created
Worksheets("Test").Range("D" & rn) = Range("E8")
Worksheets("Test").Range("I" & rn) = Range("E4")
Worksheets("Test").Range("K" & rn) = Range("D6")
Is there a s way to simplify this code (and the several more lines of similar) using For..Next and/or With function(s)? I've tried a few combinations but can't quite strike the right one.
View 2 Replies
View Related
Jan 24, 2007
I have created 2 seperate VBA's that I would like to combine and improve upon. One removes Duffs, and the other eliminates blank rows. My goal is to do a copy and paste/special, then remove the duffs, and then delete all rows that do not have data in column H.
I have tried several VBA's listed on this sight, but cannot seem to get a VBA that does everything I need it to do, and do it quickly. I have attached an example of my spreadsheet.
View 5 Replies
View Related
Jul 3, 2007
I have a combo box that is used very simply to select a column from sheet and copy that whole column down to where text ends into a specific column in another sheet. I.e whatever column is selected ends up in column B of this other sheet. At the moment I've been using If functions for each case but there must be a more efficient way of doing this; here is just a sample of my
If textseries1.Text = "Fund01" Then
With Sheets("data")
Range("C1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund02" Then
With Sheets("data")
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund03" Then
With Sheets("data")
Range("E1:E" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
There are about 75 columns in total but thought it would be a little repetitive to put it all up here!
View 2 Replies
View Related
Aug 11, 2014
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")
[Code]......
View 3 Replies
View Related
Sep 17, 2012
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
View 1 Replies
View Related
Dec 22, 2008
How can I summate cells in a column *except* for those rows which contain a specific value in another column?
=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.
View 3 Replies
View Related
May 19, 2009
I have written an SQL Stored proceedure which drags this information into excel.
All is well here, however because the information changes regularly adding and subtracting in row length it means I am having issues writing code for sums as a standard macro is absolute and not realative.
What I basically have is this ....
View 7 Replies
View Related
Jun 26, 2007
I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
View 10 Replies
View Related
Dec 17, 2008
I try to add some numbers, it will display ### symbol rather than that Value.If i give Summation for 10+10+10+10+10 the result displays ### than 50. How to resolve this?
View 15 Replies
View Related
May 25, 2009
how to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.
View 9 Replies
View Related
Mar 4, 2014
I have an issue with a Pivot table: I am using the 'show report filter pages' function and prior to this I have ensured my character length of that field is equal or less than 31 however when I hit the function the tab names are shortened to 26.
View 1 Replies
View Related
Aug 15, 2013
Attached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
View 9 Replies
View Related
Oct 8, 2008
I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.
Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation:
Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.
View 4 Replies
View Related
Jan 11, 2009
I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...
What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data
View 2 Replies
View Related
Jul 13, 2009
I'm trying to generate random numbers that will add up to a certain number and am having trouble brainstorming how to do so. For example, I'm trying to get a+b+c=6 with a, b, and c randomly generated to add up to 6.
View 4 Replies
View Related
