| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use ROUND to return an average date
Does anyone know how I would alter this using ROUND? Also, when cells in Col
H and/or Col E are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) This is what I'm trying to do..... Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in OR H13:H36 = ti, Then return the average date from E13:E36 when there is an average date but blank when cells in Col H & E are blank. Thanks in advance for any help. Christy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use ROUND to return an average date
=ROUND(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36)), 0)
-- Regards, Peo Sjoblom "Christy" wrote in message ... Does anyone know how I would alter this using ROUND? Also, when cells in Col H and/or Col E are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) This is what I'm trying to do..... Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in OR H13:H36 = ti, Then return the average date from E13:E36 when there is an average date but blank when cells in Col H & E are blank. Thanks in advance for any help. Christy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use ROUND to return an average date
Oops! To fix the DIV errors
=IF(COUNT(E13:E15)=3,ROUND(AVERAGE(IF((H13:H36={"i n","ti"}),E13:E36)),0),"") -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... =ROUND(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36)), 0) -- Regards, Peo Sjoblom "Christy" wrote in message ... Does anyone know how I would alter this using ROUND? Also, when cells in Col H and/or Col E are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) This is what I'm trying to do..... Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in OR H13:H36 = ti, Then return the average date from E13:E36 when there is an average date but blank when cells in Col H & E are blank. Thanks in advance for any help. Christy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use ROUND to return an average date
See your other post
-- Biff Microsoft Excel MVP "Christy" wrote in message ... Does anyone know how I would alter this using ROUND? Also, when cells in Col H and/or Col E are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) This is what I'm trying to do..... Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in OR H13:H36 = ti, Then return the average date from E13:E36 when there is an average date but blank when cells in Col H & E are blank. Thanks in advance for any help. Christy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use ROUND to return an average date
There is apparently a third post as well
-- Regards, Peo Sjoblom "T. Valko" wrote in message ... See your other post -- Biff Microsoft Excel MVP "Christy" wrote in message ... Does anyone know how I would alter this using ROUND? Also, when cells in Col H and/or Col E are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) This is what I'm trying to do..... Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in OR H13:H36 = ti, Then return the average date from E13:E36 when there is an average date but blank when cells in Col H & E are blank. Thanks in advance for any help. Christy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use ROUND to return an average date
I haven't made it that far!
-- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... There is apparently a third post as well -- Regards, Peo Sjoblom "T. Valko" wrote in message ... See your other post -- Biff Microsoft Excel MVP "Christy" wrote in message ... Does anyone know how I would alter this using ROUND? Also, when cells in Col H and/or Col E are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) This is what I'm trying to do..... Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in OR H13:H36 = ti, Then return the average date from E13:E36 when there is an average date but blank when cells in Col H & E are blank. Thanks in advance for any help. Christy |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use ROUND to return an average date
G'day Christy
This works as I have tested Assumed you have 3 seperate column heading IN, TI, & OT A B C 1 IN TI OT 2 A2 =SUMPRODUCT(AVERAGE(--($H13:$H36="IN"),$E13:$36)) B2 =SUMPRODUCT(AVERAGE(--($H13:$H36="TI"),$E13:$36)) C2 =SUMPRODUCT(AVERAGE(--($H13:$H36="OT"),$E13:$36)) HTH Mark. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average and Round together | Excel Worksheet Functions | |||
| Return an average date | Excel Worksheet Functions | |||
| How to return the average of the LAST 3 numbers in a row | Excel Worksheet Functions | |||
| How do I round a number after finding the average? | Excel Worksheet Functions | |||
| average annual return | Excel Worksheet Functions | |||
Linear Mode
