Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MODE
=MAX(FREQUENCY(IF(D2:D254=0,COUNTIF(OFFSET(D2,,,RO W(INDIRECT("1:"&ROWS(D2:
D254)))),"<"&0)),ROW(INDIRECT("1:"&ROWS(D2:D254)) )-1)) After calculating the max frequency of "0" occuring in a string of data, I needed the ability to return the average occurrence of the "0"s. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MODE
This array formula can replace your current formula:
=MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g)))) For the average (array): =AVERAGE(IF(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0 ,ROW(rng))),FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0 ,ROW(rng))))) Neither formula accounts for empty cells. Biff "shabbyshic" <u34829@uwe wrote in message news:734575011ddcf@uwe... =MAX(FREQUENCY(IF(D2:D254=0,COUNTIF(OFFSET(D2,,,RO W(INDIRECT("1:"&ROWS(D2: D254)))),"<"&0)),ROW(INDIRECT("1:"&ROWS(D2:D254)) )-1)) After calculating the max frequency of "0" occuring in a string of data, I needed the ability to return the average occurrence of the "0"s. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
"END mode?" | Excel Worksheet Functions | |||
Combo Box goes to edit mode even if design mode is in OFF position | Excel Discussion (Misc queries) | |||
coverting answer from Radian mode to degree mode | Excel Worksheet Functions |