Adding Constraints To Averaging Formulas Over Multiple Cells

Nov 20, 2009

I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).

For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following


(Examples)
DD-ED = Dividends Decreased & Earnings Decreased
ND-EI = No Dividends & Earnings Increased
As can be seen below this code is present for each company

What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.

So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").

So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).

View 9 Replies


ADVERTISEMENT

Excel 2003 :: Averaging Cells In Multiple Sheets And Not Including Null Or Zero Value?

Jun 24, 2012

I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.

I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.

I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!

this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!

and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?

The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.

Using windows 7, excel 2003

View 3 Replies View Related

Adding And Averaging Every Other Column In One Row?

Jul 27, 2013

I've been searching for the answer but either I do not understand or it isn't out there. I want to add every other column in one row resulting in an average of all the columns in that one particular row.

View 8 Replies View Related

Making Formulas That Automatically Update When Adding Cells

Jun 9, 2014

I want to make an excel workbook for a client. What I want to do with it is make a set of instructions telling them to add or insert as many rows in specific categories I have made for them. These options differ depending on the different duties performed. There will be an associated number (value for that duty, ex. duty might be Janitor and the number would be like 5 dollars per hour, so on and so forth), I want to make it to where there is a code that notices more rows and adds more IF statements or whatever statements are needed to keep adding those extra options. These values are connected to a data validation list that drives the if statement.

View 4 Replies View Related

Multiple Sum Formula With Multiple Constraints

Feb 17, 2010

I have listed data below for purposes of the formula:

HTML CustomerInvoice Invoice Amount
(Rep. Invoice Month)
Nike1 1,000
Reebok1 600
Adidas1 700
Puma1 600
Nike1 (250)
Reebok2 1,000
Adidas2 600
Puma2 700

How do I get the data above, into a spreadsheet like the one below? I have tried If/Than Statements with no luck. I need to total the amount of invoices during a specific month for a specific customer.

View 4 Replies View Related

VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints

Mar 18, 2009

I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.

Here is the ....

View 3 Replies View Related

Adding Multiple Cells From Multiple Sheets With Sumif Function

Jan 20, 2009

I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:

A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................

View 5 Replies View Related

Adding Multiple Cells

Nov 5, 2008

Here is what I have In cell H4 I put the following code =B5:G5

But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.

View 2 Replies View Related

Multiplying And Adding Multiple Cells To Get A Sum

Feb 18, 2014

I have a spreadsheet with 4 columns.

A, B, C, D

I want to multiply A*B and then add that to C*D

=SUM(A*B)+(C*D)=TOTAL

However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.

Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.

Also - when the total cell is empty I want it blank.

View 13 Replies View Related

Adding Cells From Multiple Tabs

Nov 3, 2008

Let me explain this as best I can:

I have an excel file with multiple tabs on it. Each tab has the exact same format with different numbers. On the last page I want to add cells from each tab and have the sum go to a cell on the last tab.

View 10 Replies View Related

Averaging From Multiple Worksheets Within A Workbook

May 13, 2008

I'm trying to do some averages for a year to date "cover" sheet. Worksheet 2 is Jan, Worksheet 3 is Feb, etc..through December. Worksheet 1 is the year to date averages. My data is a formula on each worksheet in cell B22, I thought I had the function written correctly however Excel won't take it:

=AVERAGE(IF(January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22, November!B22,December!B220, January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22 ,December!B22,""))

There are blanks,of course, until each month is filled in. Could it be because the number that is in B22 on each worksheet is the result of a formula instead of typed number

View 9 Replies View Related

Copying Conditional Formatting Formulas To Multiple Cells?

Jul 9, 2013

I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.

Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)

I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.

View 9 Replies View Related

Adding Multiple Cells Text With Special Character

Sep 3, 2013

This is my text:

Test Name1
Test Name2
Test Name3
Test Name4
Test Name5

Required result is:
Test Name1|Test Name2|Test Name3|Test Name4|Test Name5

View 8 Replies View Related

Adding Numbers Separated From Letters In Multiple Cells

Jul 7, 2009

I have column of cells containing entries such as V1, V3 and V7. I'm trying (and failing) to come up with a formula to separate the numbers from the letters and add them together: V1, V3 and V7 would together give 11.

I can use the MID worksheet function to separate my numbers from my letters on a row-by-row basis, but I can't work out how to do the whole thing in one fell swoop. =SUM(MID(A1:A10,2,2)) doesn't work, for example.

View 9 Replies View Related

Averaging Multiple Columns / Rows If Two Criteria Are Met

Apr 30, 2014

I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.

I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).

Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.

Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.

The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?

Location
Parameter
2008
2009
2010
2011
2012
2013

LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33

LocA
beta
4
9
9
8
2
9

LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65

LocB
beta
8
7
6
7
2
9

LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92

View 3 Replies View Related

Formula For Averaging Array Given Multiple Criteria

May 19, 2014

I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.

[=AVERAGE(IF(($A$3:$A$275=$P6)*($B$2:$M$2=Q$4),$B$3:$M$275))]

I have attached a sample workbook that includes the broken formula.

Average Formula Error.xlsx‎

View 4 Replies View Related

Averaging Values In Multiple Columns With Conditions

Dec 18, 2013

I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.

The first calculation is simply the average quiz grade for all students, for all quizzes.

The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.

I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.

Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.

See the attached workbook for some sample data and the expected results.

Sample Workbook.xlsx‎

View 4 Replies View Related

Consolidating/Averaging Multiple Rows & Columns

May 29, 2007

I am trying to compile a spreadsheet for my job that will find an average of all the break times and meal times that all the employees take. I have the employee list and their respective timeclock punches for the week, so I took that and found their meal and break times for each individual day, but I'm having trouble when it comes to consolidating and averaging the data. The first column lists the employees, but their names are repeated depending on how many times they punched the clock that week, and since not every punch was a meal or break, my time columns contain a lot of zeroes that aren't needed. I need a way to just average each employees' meal and break times.

View 9 Replies View Related

How To Unfix Multiple Cell References From Formulas In A Range Of Cells At Once

Jan 3, 2014

Is there a way to unfix multiple cell references from formulas in a range of cells at once?

View 2 Replies View Related

Adding Multiple Rows Using Insert Copied Cells Function?

Feb 26, 2013

I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.

I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.

View 4 Replies View Related

Averaging Different Cells From Specified Categories?

Mar 17, 2014

I want to average cells in different columns which meet a minimum criteria, i.e. > 2.0, from specified categories list in the first column. Example: I want to select a category listed in column A and average the numbers listed in columns C and F for that category which meet a minimum threshold.

View 9 Replies View Related

Averaging Cells Across Several Sheets

Jul 1, 2006

I have done alot of research in the formula realm but haven't ventured into the VBA coding side of the house due to the inexperience. I'm having issues w/ a particular calculation and hoping you all can help me out.

I hope I can explain this well.

- I have a total of 31 sheets (1-31)
- In each sheet there is a series of number (B16:L16)
- I want to take the average of all these numbers

I'm able to get the average of each sheet then take the average of those but that is not what I'm trying to do because of the following problem.

Let say in sheet1 that 5 cells are filled from the series (B16:L16) but on sheet2 there is only 1 cell filled and on sheet15 there are 10 of the 11 cells filled.

When you take the average of each sheet than average that it will not come out with the correct calculation. I'm trying to average ALL numbers combined from Sheet1 to 31 from cells B16:L16.

How can I do this. I have tried several solutions but nothing working properly. This is the one I have tried but doesn't work properly:

{=AVERAGE(If(ISNUMBER( '[Jul06AMEFVER.xls]1'!$B$15:$L$15),ABS('[Jul06AMEFVER.xls]1'!$B$15:$L$15)))}

However when I try to ":31" after the 1 it will not provide the correct value. Not sure where to go with this.

View 9 Replies View Related

Extract Numbers From String (multiple Cells) And SUM Without Adding Additional Column

Apr 10, 2013

i have data stored like:

col1

XXXX1244 50
XXXX1519 60
XXXX1244 50
xxxxx1111 10
xxxxx1519 65

the last 4 caracters are numbers. I need to test these numbers and sum the corresponding values them in a single cell without adding new column(SUMIF like).

so in the above example I need to sum all ending at 1244 or 1519, therefore the sum showed in the single cell equals 225

to extract from a single cell: =VALUE(RIGHT(D8;4))

I tried to use an array formula but it seems to crash if a blank cell is in the array

View 5 Replies View Related

Averaging Selected Cells Excluding Zero

May 13, 2009

I wanted to average selected cells excluding the value zero. Also, I wanted to make the value of cell is zero and not to display the error #DIV/0! if the denominator is zero.

A1 = Denominator

Selected cell:
A3, A5, A6, A8

View 14 Replies View Related

Averaging Dynamic Range Of Cells Across A Row

Feb 17, 2014

I have a row of dates going across in row 2, and random values going across in row 3 from D3 onwards. I want to return the average of values in row 3 until the third last non-blank cell in row 2 and do a similar thing for maximum row but just for the last 30 cells from the right (so latest 30 days). I have attached screenshot of part of spreadsheet for better illustration.

Capture.PNG

In my macro I can come up with a range for both, as below. Both lines of macro below select the correct range I am looking for.

[Code] .....

However when I try to apply them into an average/maximum function they returned an error 1004, Application defined or object define error:

[Code] .....

When I put it as following it returned me the value of Cells (3, 4) (Cell D3) instead of the average:

[Code] .....

Attached File : Capture.PNG‎

View 8 Replies View Related

Summing/Averaging Last 5 Rows (cells)

Sep 14, 2009

I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.

Tried to do it myself but succeded to do my head in only.

View 9 Replies View Related

Averaging Data Not To Include Null / Zero Cells

Mar 10, 2014

I am having difficulty trying to solution this:

Row 1 = Dates e.g. 1-Mar - 31-Mar
Row 2 = Day of Week e.g 1-Mar(B1) = Sat(B2) through 31-Mar ending at (AF)

Column A has hourly time intervals
A3 = 0:00
A4 = 1:00 etc to 23:00

Numbers fall into cells by date/day and interval up to today 10-Mar

What I am trying to do at is average the days separated by Weekdays and weekends..so the formula at AG for interval for weekday would be =AVERAGE(D3:H3,K3:O3,R3:V3,Y3:AC3,AF3) weekends (Column AH)would be =AVERAGE(B3:C3,I3:J3,P3:Q3,W3:X3,AD3:AE3)

I want to average the weekdays and weekend numbers without having to group the weekdays and weekends in a custom sort in the final column. That way when I add the data every day, it auto calculates in AG and AH. If I do it as it shows above, the AVG is skewed due to the blank cells.

Attached a sample worksheet.

View 4 Replies View Related

Excel 2007 :: Conditional Formatting And Adding Text To Cell Based On Multiple Cells

May 31, 2013

I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.

This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.

I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.

I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.

View 3 Replies View Related

Adding Two Formulas Together?

Mar 24, 2014

how do i add this =LOOKUP((E12+G12),{0,1500,6000,15000,55000;3.4,2.9,2.4,1.9,1.4})%*(E12+G12)+0.2 Into this fomula =IF(AND(E3>0,J3="Yes"),((E3+G3)*3.4%+0.2),0)

View 1 Replies View Related

Adding Numbers In Multiple Cells Where Individual Cells Contain Both Numbers And Text?

Jan 8, 2014

I have a column that looks like the following and I need to add the numbers:

27 skids
31 skids
56 skids
13 skids

The unit "skids" is constant. The answer I am looking for is "127" or "127 skids"

View 3 Replies View Related







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