Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help with averages and if then statements
I sure hope someone can help me this time.
I have several years of daily data and Im trying to determine if an average goal was met on a daily basis. The end result: would be In/ Out/Absent Column A is the date and Column B has numbers, blanks and zeros. The blanks tell you that there were no samples for that day, and zero means that there was a sample but no data. To come up with the In/Out/Absent, I need to take the 7-day prior average which includes today and the next two sampled days within the next 7 days which also includes today. The threshold for In is =15 and the threshold for Out is <15. If there are 13 days of no data (looks blank) the result should be absent Heres an example of the end result A B C Date Sample Result 19-Jun-98 42.00 20-Jun-98 21-Jun-98 22-Jun-98 23-Jun-98 0.00 24-Jun-98 25-Jun-98 In 26-Jun-98 0.00 Out 27-Jun-98 8.00 Out 28-Jun-98 Out 29-Jun-98 Out 30-Jun-98 4.00 Out 1-Jul-98 0.00 In 2-Jul-98 1.00 In 3-Jul-98 In 4-Jul-98 10.00 In 5-Jul-98 In 6-Jul-98 In 7-Jul-98 Out 8-Jul-98 Out 9-Jul-98 Out 10-Jul-98 Out 11-Jul-98 Absent 12-Jul-98 Absent 13-Jul-98 Out 14-Jul-98 In 15-Jul-98 In 16-Jul-98 In 17-Jul-98 In 18-Jul-98 In 19-Jul-98 9.00 In 19-Jul-98 12.00 In Thanks for the assist |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help with averages and if then statements
My example is incorrect because i didn't account for the average, I took the
straight number to show the example. sorry "Lisa" wrote: I sure hope someone can help me this time. I have several years of daily data and Im trying to determine if an average goal was met on a daily basis. The end result: would be In/ Out/Absent Column A is the date and Column B has numbers, blanks and zeros. The blanks tell you that there were no samples for that day, and zero means that there was a sample but no data. To come up with the In/Out/Absent, I need to take the 7-day prior average which includes today and the next two sampled days within the next 7 days which also includes today. The threshold for In is =15 and the threshold for Out is <15. If there are 13 days of no data (looks blank) the result should be absent Heres an example of the end result A B C Date Sample Result 19-Jun-98 42.00 20-Jun-98 21-Jun-98 22-Jun-98 23-Jun-98 0.00 24-Jun-98 25-Jun-98 In 26-Jun-98 0.00 Out 27-Jun-98 8.00 Out 28-Jun-98 Out 29-Jun-98 Out 30-Jun-98 4.00 Out 1-Jul-98 0.00 In 2-Jul-98 1.00 In 3-Jul-98 In 4-Jul-98 10.00 In 5-Jul-98 In 6-Jul-98 In 7-Jul-98 Out 8-Jul-98 Out 9-Jul-98 Out 10-Jul-98 Out 11-Jul-98 Absent 12-Jul-98 Absent 13-Jul-98 Out 14-Jul-98 In 15-Jul-98 In 16-Jul-98 In 17-Jul-98 In 18-Jul-98 In 19-Jul-98 9.00 In 19-Jul-98 12.00 In Thanks for the assist |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averages formula/0 | Excel Worksheet Functions | |||
calculating averages and using conditional "if" statements | Excel Worksheet Functions | |||
Averages that include cells with IF statements | Excel Worksheet Functions | |||
averages formula | Excel Worksheet Functions | |||
averages formula | Excel Worksheet Functions |