Script For Calculation Of Sum Based On Two Conditions

Feb 21, 2014

I have the following spreadsheet as an example:

A
B
C
D
E
F
G
H

1
Article/hours
10
27
33
15
33
40
40

[Code] ........

Column A contains codes for certain work activities.Row 1, from Column B and beyond contains number of worked hours.The matrix is populated with letter "D" to mark activity for particular work code for particular amount of worked hours.

My goal is to have a formula/script that analyzes this matrix, and returns a total amount of worked hours per work code. In other words, if a cell contains the letter "D", the amount of hours in Row1 for that particular column should be added to the total amount for that particular code, let's say code "501" as an example. For this example, the result should be a cell, lets say "A8", that contains the total amount of hours spent on code 501: in this example - 213 hours.

View 5 Replies


ADVERTISEMENT

Difference Calculation Based On Other Column - Reset Based On Non-Zero Value

Apr 4, 2014

I require calculating difference between two columns. (Actual and Estimated).

Difference between these two numbers are calculated in third column, but it is reset every time actual number is non-zero.

Scenario:-
-Estimated Numbers are always followed by an actual number.
-After actual number is obtained, estimated numbers till that date will be added and deducted from actual number.
-After this, again estimated number will be added till next actual number is obtained for deduction.

If estimated number is not followed by actual number, then no need for difference calculation.

View 3 Replies View Related

Calculation Based On Date

Jul 16, 2013

How calculation can be made based on the date. I have 2 sheet (MATTER and REPORT). All productions made within sheet MATTER. Then I will produce report to be printed in sheet REPORT. I only include the date and calculation every matter will be automatically made based on date and item.

View 6 Replies View Related

Calculation Based On % Of Cells

Oct 29, 2008

I need a formula that I can place in, lets say, Cell A1 that will reference Cells B1, B2, B3 and C1, C2, C3. These all have numbers & %'s in them. However, I want to "weight" the B cells by the percentages in C cells. The problem I am having is that when I change the %'s nothing is changing in the output...almost like I was doing nothing but averaging B1:B3 without the % to adjust.

This is how I envision it, but my calculation may be wrong of course :
A1 = average(sum(B1,B2,B3)*C1, sum(B1,B2,B3)*C2, sum(B1,B2,B3)*C3)

The Goal:

This would allow me to change the output depending on the percentage. So if I am looking at days to an appointment and the B cells represent areas of a city: B1 = 10, B2 = 5, B3 = 2 the average would be 5.6, however, I know that B1 city has more "weight" to it with our customer base so I want B1 to be closer to what will show as days out (which is obviously important as 10 days is not good). So I would assign C1 = 60%, C2 = 30%, C3 = 10%. This should pull the average more to 7 or 8 as it would lean more towards the B1 value. I hope this makes sense.

I did try going more of a route where I did something like =average(B1*C1,B2*C2,B3*C3) but that lowered all the original values instead and averaged them instead of pushing the number more towards the higher weighted % of B1 cell value. I can send an example spreadsheet if I am losing anyone.

View 8 Replies View Related

Calculation Based On Variable

Jun 28, 2006

Im trying to access data from 2 different cells (say A1, B1), its for calculating transformer hotspots. The cooling method of the transformer is naturaly air cooled oil.. until the winding temp reaches 75C ( I have winding temp data laid out on a column) then the fans come on and some constants in the formula change (need to read from the other cell, say B1) so it switches, but the fans stay on UNTIL the winding temp is 50C and then go off, so the characteristics change back to the previous one, so I need to switch back. How do I go about doing this... my current method starts working when it reaches 50 instead of waiting the temp to go to 75 then fall to 50 then do it... im a bit confused.. maybe i shouldnt be doing this on excel... ow well

View 9 Replies View Related

Calculation Based On Criteria

Jul 25, 2006

I'm trying to calculate a price for any given mileage. The pricing strategy is as follows:

For any journey 2 miles or less, the price is £9
For any journey between 2 or 3 miles, the price is £10
For any journey above 3 miles, the price is £10 for the first 3 miles plus £2 for every additional mile.

So, for example, the price of a 1.5 mile journey is £9 and the price of a 3.5 mile journey is £11.

View 6 Replies View Related

Calculation Based On Cell Value

Dec 17, 2007

I tried all those search and have no idea what to look for, sorry.

I have attached a single sheet with Footpath Budget for you to see.

In J6, we put in Y or N for Yes or No if there's a nature strip along the footpath.

Now, in cell G49 gives us the total square meters of nature strip.

What I want to do is if I put N in cell J6, cell G49 should be empty or zero because N means there's no nature strip. But if I put Y in J6, then cell G49 can give us total squares from cell C43.

So what formulas can I put in cell G49?

View 3 Replies View Related

Trying To Create Calculation Based On Particular Criteria

Jan 29, 2014

I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet.

I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.

I have attached the workbook I am working with : Production Report.xlsx‎

View 9 Replies View Related

Calculation Based On Colored Cells

Dec 4, 2008

i have a sheet which has name of students (A2:A10), B1:E1 has project name. and the projects are catagorised by color red=high,green=medium, yello=low. i have given weightage to these color red=80%, green=50%, yello=20%.
similarly the involvment of the students in each project is shown by these three color in respective cell. like B2 is red, C2 is red,D2 is blank, E2 is green. (the weightage is same as above). in column F i want the total utilisation of each student, which is simply sum of product of weightage given to these colors.
e.g.

project 1project 2project 3project 4utilisationsam1.38joe0.54nick0.72

what i want is to calculate this utilisation number, but i want my sheet to be shown in the same color format.
i am sure that there is some solution for this.

View 9 Replies View Related

Calculation Based On Dropdown Box Selections

Apr 13, 2007


I have been trying to put this together but am confused since I am new and this seems complex to me.

This is what I am trying to accomplish:

IE.

$F6 = 100

If $I6 is >1 AND $I7 is = 1 AND $I8 is =1 AND $I9 is =1 then $J6 =$F6
If $I6 is >1 AND $I7 >1 AND $I8 is =1 AND $I9 is =1 then $J6 =.5*$F6
If $I6 is >1 AND $I7 >1 AND $I8 >1 AND $I9 is =1 then $J6 =.33*$F6
If $I6 is >1 AND $I7 >1 AND $I8 >1 AND $I9 >1 then $J6 =.25*$F6

I am trying to divide a dollar amount equally between "Officers" if they were part of a transaction. By selecting the officers it would divide the total amount equally.

Is this even possible?

View 9 Replies View Related

Calculation Based On Name In Cell Text

Jan 23, 2008

I'm trying to create an if statement to check to see if a cell has a value in it and return a math function. My problem is that some of the values contain more that just the name searched for. This is a basic example

A B C
10 Williams DXT if(B1="Williams",A1*.8,if(B1="Johnson",A1*.85,1))
20 Williams DPX if(B2="Williams",A2*.8,if(B1="Johnson",A2*.85,1))
30 Johnson AMV if(B3="Williams",A3*.8,if(B1="Johnson",A3*.85,1))
40 Johnson ACS if(B4="Williams",A4*.8,if(B1="Johnson",A4*.85,1))
50 Smith TWN if(B5="Williams",A5*.8,if(B1="Johnson",A5*.85,1))

View 4 Replies View Related

Sum Based On 2 Conditions

Dec 1, 2007

I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)

View 4 Replies View Related

If String And Formulas (calculation Based On The Selections)

Aug 27, 2009

in cell A1 the user will select from a drop down list 5.50, 11,22, 33, 55. in cell B1 user selects from the drop down list a number 1 - 9. now in cell c1 I wont it to do some calculation based on the selections.

If b1=3, then A1*.20, if B1=2, then A1*.30, If B1=1, then A1*.50 and if B1 is greater then 3, post 0, if B1 is empty, then leave c1 empty.

View 5 Replies View Related

Return Text Based On Calculation Result

Dec 18, 2007

I need an excel function for this...

Say,
Cell A2= 8
Cell A3= 1

If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?

View 2 Replies View Related

Calculation Value Based On Month Name In Adjacent Cell

Feb 28, 2008

I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B

September: 26c
October: 13.5c
November: 43c
December: 66c
e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)

View 3 Replies View Related

VLOOKUP Based On Two Conditions?

Aug 16, 2013

My challenge is pulling in a data element from a file to an exisiting file based on two criteria.
Example: give me the value in cell e1 if cell b1 matches AND

File 1
A B C
1 H12377 03/05/2013 123
2 H12377 03/27/2013 276
3 H32389 05/03/2013 335

File 2
H12377 03/27/2013 _________

how do I get C2 value from file 1 (276) into file2 since column A is not unique but column A plus Column B is unique?

View 5 Replies View Related

Average Based On Conditions?

Jul 1, 2014

I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.

View 5 Replies View Related

Lookup Based On A Few Conditions

Mar 13, 2008

I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).

View 9 Replies View Related

Sum Based On Multiple Conditions

Aug 28, 2009

I am trying to sum numbers based on three counditions.

My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*

The yellow cell contains the formula I am using.

View 4 Replies View Related

Sum Based Conditions In Different Sheets?

Dec 11, 2012

I'm creating stockportfolio's based upon historical stock data. I have 300 stocks with monthly observations. Stocks are assigned to a portfolio based upon their return in the previous 6 months. I need the top 10% stocks in terms of return in previous 6 months (so 30 stocks) in one portfolio and the lowest 10% stocks in terms of returns in previous 6 months returns (so again 30 stocks) in another portfolio. Each portfolio is just a sum of the returns of the stocks that are in there.

First sheet: rows contain the return a stock had in the 6 months before the month in the left column (so january 2006 contains the return a stock would have had from july2005 -december 2005). This is done for 300 stocks. Second sheet: contains the return the stock had in that actual month (so january 2006 just contains the january 2006 return) The third sheet is where I want to create the decile portfolio's. This means that for every month i am creating 10 portfolio's: Each month "portfolio 1" should contain the sum of the returns of the stocks with the 30 highest returns in the past 6 months. "Portfolio 10" should contain the sum of the returns of the stocks with the 30 lowest pas 6 month returns. (30 is 10% of 300: that's why it is called decile portfolio)

So I should sum things from sheet 2 with the condition referring to sheet 1 where the returns of the past 6 months are displayed. I am trying formula's like =SUMIF(RANK...) but am getting no results. Somehow excel should select the right stocks and sum the 30 returns. I'm totally lost after a lot of trials.

View 1 Replies View Related

Adding Based On Certain Conditions?

Mar 6, 2013

I am doing a financial analysis for a hospital. I want to know how to add something to a number based on conditions. Base pay is $300 for the first 10 patients. If more then ten patients >11-20 add 40 dollars per visit if greater than >21 add 50 dollars per patient

View 3 Replies View Related

Get A Result Based On Several Different Conditions

May 20, 2009

New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.

The following is a table which shows a salesmans sales. He or she has sold to 8 customers.

What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.

1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40

2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25

3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...

View 6 Replies View Related

Lookup Based On Two Conditions ...

Jul 13, 2009

I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1.

The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H.

So I need to return something like this: ...

View 14 Replies View Related

Sum Based On Hundreds Of Conditions

Aug 3, 2012

I am tasked with making a matrix that I cant seem to solve.

I have solved it on a small scale using

=SUMPRODUCT($E$5:$E$19,--ISNUMBER(SEARCH(O11,$C$5:$C$19)),--ISNUMBER(MATCH($D$5:$D$19,{"One","Two","Three"},0)))

The issue is that I need where {"One","Two","Three"} is to contain between 3 and 334 conditions.

Basically we have a data table that contains "channels" that have multiple affiliates under it. Then we have a data table by affiliate by country and revenue... I want revenue by Country By Channel...

View 3 Replies View Related

Popup Based On Two Conditions

Sep 25, 2012

I have this vb script which basically states if a value in column G is less than 0 then show a message box as below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
End If
End Sub

What I would like to do is extend this script so that another message box pops up based on two conditions

i.e. if a cell in column F shows string "accounts use only" AND value in the adjacent cell in column G is > 0 then MsgBox "you have entered a positive figure - are you sure this is correct?"

View 5 Replies View Related

Macro With If / Then Based On 3 Conditions

Aug 10, 2013

I'm trying to adjust my macro to fill the interior cell color of a row if there's a non-blank row above and below it (i.e. if there are 3 or more non-blank rows together, then fill the 2nd, 4th, etc. rows. If there are only 1 or 2 rows together, then don't fill the cells.

However, what I'm getting is filling ever other row, with this code. There seems to be an error in how I'm trying to use and If condition with 3 criteria.

Code:
Sub colorin()

Dim LastCol As Long
Dim r As Integer
r = 6
With ActiveSheet
LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column

[Code] ....

Here are some sample results (pretend cells with red text are actually cells with interior color, black text is an unfilled cell).

Rank
Name

1
Jones, Some

[Code] ......

What I'm trying to achieve is: (again, red text actually represents filled cells - can't get sample shot of actual filled cells to copy into forum post).

Rank
Name

1
Jones, Some

[Code] ...........

View 6 Replies View Related

Display Value Based On 2 Conditions

Nov 6, 2009

I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course.

The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....

View 9 Replies View Related

Lookup Based On 2 Conditions

Dec 22, 2006

In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.

View 4 Replies View Related

Add Sheets Based On Conditions

Dec 29, 2006

i am not able to solve this problem.

1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table

2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month.

3- Reference table has the list of the same data record ( numbers ) .

Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following.

a- Check for the record in the reference table , if doesnt exist then add the record in the reference table

b- create another sheet same as the sheet " A" (duplicate of
A) and name it the new record number

b- Plug the new record number in the cell B2 of the new sheet

c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to
O8)from sheet A

View 5 Replies View Related

Automate Calculation Of Output Based On User Input

Nov 2, 2009

What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)

I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved