hi experts, I have the following statitics data as below 1st 2nd 3rd 16-Jan 505

Question

hi experts,

I have the following statitics data as below

               1st    2nd     3rd

16-Jan    505    080      877

1-Feb     834    403      438

16-Feb   076    235      225

1-Mar    182    047      505

I need a formula that would have a result as below

result 1.  047

               076

               080

               182

               and so on

result 2. that would count number of duplication per reading like

               505 = 2

result 3. forecast a date when the reading would occur next like

               505 = next date reading would occure??

thanks a lot, :*(-)^

Explanation / Answer

Maybe I can help with #1 and 2, but 3 is a bit beyond me in the formula world - hopefully someone else can assist with that.

#1

Assuming your table of numbers is in $B$2:$D$5

Assuming you want the result 1 numbers in a column beginning on row 2 (so that row 1 can have a label), then a formula like this should do the trick. I put my formulas in column G, so at G2 I have this formula:

=SMALL($B$2:$D$5,ROW()-1)

The ROW()-1 evaluates to a 1 on row 2, increments to 2 on row 3, etc.

Fill the formula down the sheet until you start getting #NUM! errors.

If the table could grow in size and you plan ahead and fill that formula down far enough to cover the entire table at its largest size, then you can hide the #NUM! errors by using this formula:

=IFERROR(SMALL($B$2:$D$5,ROW()-1),"-")

The dash will let you know that the table still has room for growth and that there is a formula waiting to deal with that growth.

#2

The formula results from #1 give us a nice, simple way to count frequency. With those formulas in column G starting at G2, I put this formula into H2 and filled it down, it has a built-in check so that it doesn't report on number of dashes found:

=IF(ISNUMBER(G2),COUNTIF(G:G,G2),"-")

again, the displayed dash reminds you that the table has room for growth and there is a formula in place to deal with it.

#3 - again, apologies, but a real statistician is going to have to chime in and come up with that solution.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.