Sumproduct Formula: Multiply Monthly Values With A Maximum Value In Any One Period
Jun 16, 2009Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.
View 2 Replies
ADVERTISEMENT
Minimum And Maximum Value In Sumproduct Formula
Dec 1, 2008Is there a simple way to find the min and max value for my formula below
HTML =SUMPRODUCT((Data!$J$2:$J$10000="E700")*(Data!$M$2:$M$10000>'Rental Rates'!$A3)*(Data!$M$2:$M$10000<'Rental Rates'!$B3)*(Data!$M$2:$M$10000))
Complex VBA / Formula To Multiply Values
Aug 9, 2012I have a complex request which I think can be done either via vb or a macro, not sure which is best?, will try to explain:
The table shows a list of items that make up various BOM. there are quantities of each item required at the different BOM level
0 = Top level
1 = Level Below
2 = Level below that etc, etc
If the top level (0) has a quantity greater than 1 then all the items at the lower levels should be multiplied by that quantity
THEN move to the level 1's and whatever the quntity is in the level 1 field, multiple all lower levels by that number
if the quantity fields are blank, then always multipy below by 1
This needs to continue on down the chain of levels
So basically you end up with the correct quantites of each item at each BOM level.
Excel 2003 :: Time Card / Sheet For Semi-monthly Pay Period With Overtime Calculation
Nov 19, 2012I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
Excel 2003
H
I
O
P
Q
R
S
1
Start Work
Time Out
Day Count
[code].....
Variable Monthly Usage With Maximum Limit?
May 29, 2014i am working on model where customer is billed based on usage of the equipment. Usage is measured in cycles per day. Assuming there are 30 Days a month. i am trying to build a spreadsheet where if i increase or decrease the monthly usage it automatically update the sheet. Remember there is limit on maximum number of times an equipment can be used.
Like for Example
Maximum life of one equipment. 3000 Cycles
Daily usage : From 1 cycle per day to 10 cycles.
Monthly usage = 30 Days * (daily usage)
Now the problem is each month new equipment is leased. so i have to keep track of each month's equipment how much it has been used and how much left. I have attached the example spreadsheet.
Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total
Mar 6, 2013I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
Sumproduct: Count Number Of Leaves Within A Specified Period
Nov 5, 2009=SUMPRODUCT(SLVL!$B$6:$B$49>=$A$2)*(SLVL!$B$6:$B$49<=$B$2)*(SLVL!$A$6:$A$49=A12)
I'm sure that there had been an error somewhere but I can't figure it out. I'm getting zero value. Situation: need to count number of leaves within a specified period, thus I opted to use Sumproduct.
Incorrect Sumproduct Array: Sum Up The Total Number Of Hours Consumed On A Particular Activity Within A Specified Period
Oct 23, 2009I am trying to sum up the total number of hours consumed on a particular activity within a specified period.
=SUMPRODUCT(($C38:$C176>=$B7)*($C38:$C176<=$C7)*($B38:$B176=$D5))
Unfortunately, the formula above gives me the total number of activity and NOT the total number of hours within a specified period. I attached the file I'm working on as reference.
Difficulty Adding Minimum And Maximum Values To Existing Formula?
Mar 12, 2014I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
Formula Not Working For Sumproduct When Only Looking Up 2 Values
Jan 19, 2014I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this
[Code] .....
Full formula is below:
[Code] .....
Sumproduct Formula - How Do I Exclude Values
Apr 14, 2009I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.
=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$B$119:$B$417))
SUMPRODUCT Formula Throwing Out #VALUES!
Jun 9, 2009fix cell E8-E19 (totals). I don't think its anything to do with the date format.
View 2 Replies View RelatedSUMPRODUCT With 2 Criteria, But One Is A Maximum Number Of Items To Include
Jan 14, 2009I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not all-inclusive.
Column B - Lots of numbers, the SUM range
Column C - Yes and No flags. Y or N
C1 = maximum number of matching items to include.
So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.
Sumproduct Formula To Exclude String Values
Jan 22, 2014Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF
I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.
Below is the entire forumla just in case.
11111111FT04comp
comp
tri
11comp
11tri
tri11111111FRP06
=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)
*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))
Excel 2000 - If A1 Equal Or Less Than 1000 Multiply By B1 Otherwise Multiply By C1
Oct 30, 2013Excel (2000), what formula would I use to accomplish the following?
If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.
How To Add 3 Horizontal Monthly Values And Create Quarterly Values
Oct 24, 2013I have a data series running horizontally, which gives me monthly values e.g.
Jan 10 - Feb 12 - Mar 11 etc. Imagine the months are in row 2 beginning from column 2, and the values are in column 3.
I would like a formula which adds the three values, and in row 6 column two gives me the total for the quarter (10 + 12 + 11) and displays 33.
The next quarter value, I would like in row 6 column 3, etc.
Multiply All Values In Range
Nov 22, 2006I have a question regarding a vba formula in excel. I am trying to take a whole row ( range) of data and multiply each cell within that range by -1 if a particular cell is an Asset or Liability.
Function AFTER_REFRESH(Argument As String)
AFTER_REFRESH = True
If Range("G5:H5").Item(1, 55) = "LEQ" Then
Range("G5:H5").Value = (-1) * Range("BM5:BN5")
MsgBox "Yo"
Else
End If
End Function
Would I have to use an array?
How To Convert Text Into Values And Then Multiply
Sep 18, 2013I have 2 columns - one that has numbers and the second that has text such as A, B, C, D,and E. I need to be able to convert the text into values such as 1,2,3,4,and 5 and then multiply with the first column that has value and display the resultant.
View 8 Replies View RelatedMultiply Values Accross Different Rows
Nov 28, 2008I am trying to build a formula that will give me the 'Final Qty' listed. It needs to be based on 'indentation' level, listed in the 'Belongs To:' column. Exammple: Part 130-0014-02A belongs to 130-0647-00B, which in turns belongs to 139-0086-00D, so mathematically, 4*1*10 (order qty) gives 40.
Can this be done by using some sort of combination of Sumproduct, Index, Match, Sumif formulas? ...
Sum & Multiply Values Within Criteria Range
Jul 17, 2009Please see the attached .xls.
I've tried to replicate my formula (result is in cell J5) via an array formula but keep getting stuck.
Basically there is a 2 dimensional array of weights (range1, limits)
each range in range1 and "limits" has a midpoint
a constant value (Fixed Amt) is added to each midpoint in range1
then multiplied by another constant value (Inflation Index)
The value is then capped by corresponding value in the limits range
this is repeated for all points in the 2 dimensional array (range1,limits) (essentially weighting the results)
and the sum is returned.
I've been able to get most of the way with an array formula, but end up collapsing the array when I use the min function.
Add And Multiply Values Based On Date Reference
Apr 27, 2009I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.
Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.
The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.
In the next post I will include the Amortization schedule.
In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).
Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Multiply 2 Values Based Upon Corresponding Column Cells
Oct 15, 2009I'm absolutely new to Excel and I've been trying to workout a formula to to test two conditions if true and derive the data from a 3rd column. In my attached excel file there's a Grade (A) , Status (B) and Mid Point (C) Tables. I'd like it to be formulated in a sense that forexample:
IF Grade= 1 AND Status= Q4 RETURN =3750*0.25
IF Grade= 1 AND Status= Q3 RETURN =3750*0.5
IF Grade= 1 AND Status= Q3 RETURN =3750*0.75
IF Grade= 1 AND Status= whatever (I mean whatever I input in this cell then it would retrun 3750 ofcourse satisfying the condition grade= 1) RETURN =3750
IF Grade= 2 AND Status= Q4 RETURN =5500*0.25
IF Grade= 2 AND Status= Q3 RETURN =5500*0.5
IF Grade= 2 AND Status= Q2 RETURN =5500*0.75
IF Grade= 2 AND Status= whatever (I mean whatever I input in this cell then it would retrun 5500 ofcourse satisfying the condition grade= 2) RETURN =5500
................
Data Validation And Multiply Values Based On Choice
Mar 11, 2014I've got a spreadsheet and I've used data validation to look at a list on another tab.
I need to be able to multiply values out based on what i choose on the drop down menu.
I've attached a sample spreadsheet to explain : tasks.xlsx
Multiply Column Of Values By A Random Number Within Range?
Apr 20, 2012I have a column of values I need to multiply by a random number (percentage) within a range of numbers (15%-22%).
How can this be done?
Single Cell Two Numerical Values - Multiply In Different Columns
Jun 27, 2014I am looking for formula , single cell two numerical values , multiply in different columns
example :
Cell (A1) contain: =350,23+25,59 (two values in a single cell)
request:
in (B2) only first value so 350,23 multiply (x) by fixed number &in (C2) only second value so 25,59 multiply (x) by fixed number
Multiply & Round Formula
Mar 21, 2008I am having a calculating issue with a formula I'm trying to use.
What I am trying to do is to multiply the " sum of one cell" with "another cell" for a total.
The following are the cells and data involved.
A12 = 93 C12 = $44.76
A19 = 93 C19 = =sum(C12*0.05) D19 = =sum(A19*C19)
The formulas looked pretty straight forward. My problem is the total I got in D19 was not what I calculated on a calculator. C19 is $2.24 and the above formula gave D19's total as $208.13 but the calculator's total is $208.32.
I can't figure out what is causing the different totals. If there is another formula I should use for this.
Excel 2007 :: Multiply Series Values On The Fly Before Presenting In Chart?
Jun 4, 2014I would like to multiply a serie in 'series values' (before presenting on a chart) with a factor (in a formula like ='[1]name'!$V$2:$V$148)
in other words I would like to do something like
=('[1]name'!$V$2:$V$148)*50
but I don't seem to succeed with the correct syntax.
In Excel searching for "multiply series values" under "edit series" there is to read:
"Use this option to include additional data series on the chart or to modify the name and values of existing data series without affecting the data on the worksheet."
So what I want to do seems to be possible.
Excel 2007 version 12.0.6611.1000
I know I can create a temporary column with the multiplied values but THIS IS NOT the way I want to solve the problem.
Formula To Get Data Of Specified Period - More Actions For Each Day
Jan 15, 2014I want know the formula for below issue. I have number of of actions done for each day for a month or more.
If I select a particular period for example 01jan14 to 10jan14 i want to get all actions done in this period.
Determine Formula To Know Period Time?
Oct 9, 2011I am trying to determine a formula to know a period time for example i started to test a unit at 10:20 am of 10/02/2011 and i would like to add 52 hours of test and i would like to know when and at what time will finish the test?
View 3 Replies View RelatedMultiply 2 Columns When Another Formula Exists To Add In?
Aug 22, 2014So I have the first part of my equation and it works fine. The equation is in Column D.
=IF(ISERROR(FIND("Van",A7,1)),"",B7*C7+50)
What I want it to do is: Combine the above for Col D with: if the Unit Name Column (A) says anything EXCEPT Van, I want it to multiple Cost (Col B) by QTY (E) and put the total in Col D.
