Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to figure average efficiency based on criteria
Need help with an IF-THEN formula to calculate an avg. efficiency base
on 2 different columns. I have created a sample of what I need hel with and attached it as a file. Thank Attachment filename: book1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=51420 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to figure average efficiency based on criteria
Hi
not looking at your example but if you have three columns (A,B,C) and want to calculate the average for column C based on the conditions for col. A and col. B use the following array formula (entered with CTRL+SHIFT+ENTER): =AVERAGE(IF((A1:A100="cond_1")*(B1:B100="cond_2"), C1:C100)) -- Regards Frank Kabel Frankfurt, Germany Need help with an IF-THEN formula to calculate an avg. efficiency based on 2 different columns. I have created a sample of what I need help with and attached it as a file. Thanks Attachment filename: book1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=514206 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to figure average efficiency based on criteria
Since I'm not so swift when it comes to array formulas, I developed
quick bit of code you can run after you have entered new data. Jus put it in a module and run it from a button. Code ------------------- Sub DoAvg() Dim i As Long Dim iRowLast As Long Dim iRowFirst As Long Dim rNum As Double Dim rDen As Double Dim strLast As String strLast = "Nothing" rNum = 0 rDen = 0 iRowFirst = Range("L65536").End(xlUp).Row + 1 iRowLast = Range("A65536").End(xlUp).Row + 1 For i = iRowFirst To iRowLast If strLast < Cells(i, 1).Value & Cells(i, 3).Value Then If strLast < "Nothing" Then If rDen < 0 Then Cells(i - 1, 12).Value = rNum / rDen * 100 Else Cells(i - 1, 12).Value = "Error" End If End If rNum = 0 rDen = 0 End If rNum = rNum + Cells(i, 9) * Cells(i, 11) rDen = rDen + Cells(i, 9) strLast = Cells(i, 1).Value & Cells(i, 3).Value Next End Su ------------------- I tested it on your workbook and it worked fine. I did multiply th average by 100 to get percent. Take this out if you format that colum as %. But, if Frank's suggestion works for you, by all means use it! -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE BASED ON 2 CRITERIA | Excel Worksheet Functions | |||
average based on criteria | Excel Worksheet Functions | |||
need to average based on criteria | Excel Discussion (Misc queries) | |||
Using a time value in an Excel formula to figure an hourly average | Excel Discussion (Misc queries) | |||
average one column based on criteria from another | Excel Discussion (Misc queries) |