Average - multiple parameters

[Nicnak]

Hi All

I need to average out rates per within a particular status and taking in to account the number of days contracted for the month. I have one already in place, BUT there are to many averages to get the correct answer (as ive had to average out per status type and then average the 3 status to get one average) and if there is a lot of movement for the month the rate can be up to $30 discrepancy. So I was hoping there is a formula that I can use, that cuts out some steps. I know it will be a long one but here as a example of what I need to average:

It needs to be broken down in to Booking type and into month
Booking type
Short Stay
6 month
12 months
etc

Headers are
Booking type
Weekly rate
12 Months - under each month, it is split into 3 status (inroom/reserved/history). While the status is in 3 different columns, for this purpose I'm only concerned about the average rate as a whole for the month.

So if the booking type is equal to the same booking type, take the weekly rate/7 to get the daily rate, taking in to account how many days in the month they are contracted for (within the 3 statuses). Not averaging any columns that are zero.

this is the current formula: =IFERROR(AVERAGEIFS('Data Control'!$K:$K,'Data Control'!$H:$H,'Average Weekly rate'!$A86,'Data Control'!$T:$T,">=1"),0)

Hopefully I've explained this without to much confusion.

 

[Nicnak]

Re: Average - multipul parameters

bump

 

[FDibbins]

Re: Average - multipul parameters

You really should wait at least a day before "bumping" your thread. many members look for 0-reply threads, yours now has 1 reply after you posted

1st mistake you are making is to take an average of averages - that is mathematically incorrect

You may need to add a helper column to pull the data you need to look at. Can you post some sample data please?

 

[Nicnak]

Re: Average - multipul parameters

You really should wait at least a day before "bumping" your thread. many members look for 0-reply threads, yours now has 1 reply after you posted

1st mistake you are making is to take an average of averages - that is mathematically incorrect

You may need to add a helper column to pull the data you need to look at. Can you post some sample data please?

Sorry, I'm very new to this. I've tried to cut & paste data in the message, but is didn't work. Is there another way?

 

[FDibbins]

Re: Average - multipul parameters

Try to copy paste, it sometimes works OK

 

[Nicnak]

Re: Average - multipul parameters

Jan

Feb

Mar

contract start

contract finish

contract type

weekly rate

Inroom

Reserved

History

Inroom

Reserved

History

Inroom

Reserved

History

15/01/2017

15/07/2017

6 Months (SP1)

$294.00

14

31

28/01/2017

28/01/2018

12 Months (Sem 1)

$224.00

3

28

31

1/02/2017

1/02/2018

12 Months (Sem 1)

$259.00

28

31

10/01/2017

18/02/2017

Short Stay

$319.00

21

17

5/01/2017

31/01/2017

Short Stay

$349.00

26

31/01/2017

31/07/2017

6 Months (SP1)

$424.00

1

28

31

15/02/2017

2/03/2017

Short Stay

$319.00

13

1

2/07/2016

2/01/2017

6 Months (Sem 2)

$300.00

2

28/02/2017

28/08/2017

6 Months (Sem 1)

$300.00

1

31

Jan-17

Feb-17

Mar-17

Apr-17

May-17

Average Weekly Rate

Average Weekly Rate

Average Weekly Rate

Average Weekly Rate

Average Weekly Rate

6 Months (Sem 2)

12 Months (Sem 1)

Short Stay

<tbody>
</tbody>

Example