calculating hit rate

Hi guys

I have an assignment in which I needed to estimate a multinomial logit model in SAS and I wanted to calculate the hit rate of my model. Since SAS doesn't offer a function to calculate the hit rate I wanted to calculate it in excel.

In the attachement I've added the excel file in which I've put the estimated probabilities which I've already calculated with SAS. Also included are the following variables:
- household: the number of the household that buys a product
- week: week number
- alternative: number of the product (there are 7 products)
- choice dummy: equal to 1 if the alternative is chosen and equal to zero otherwise
- choice variable: indicates the chosen product

hit rate calculations 2.zip


Now in order to calculate the hit rate I need to look at each week individually.
In each week the highest probability must be selected (because a MNL model predicts that the product with the highest probability will be chosen by the consumer) and then I need to check if the choice dummy is equal to 1 for that alternative. So basically I check if the predicted choice (the product with the highest probability) is also the real choice (the alternative for which the choice dummy is equal to 1).

So basically I need to create a dummy variable that is:
= 1 when a product, that has the highest probability within a week, has a value of 1 for the choice dummy
= 0 otherwhise

All the predictions that are correct need to be summed up and then I need to turn this into a percentage that indicates how good the model makes predictions (how many times the predictions were right). But if you can just help me with creating the dummy then I can continue on my own

Can someone help ? Cause my assignment is due to Sunday and I've been at this for ages, but I just suck at working with excel ...

Many thanks in advance!!

Greetings Michel

Hi
I'm a bit confused about your example
The highest prob from A2 to A8 is in A8 = choice 7 OK
The highest prob from A9 to A15 is in A10 = choice 2 OK
But then
The highest prob from A16 to A22 is in A22 = choice 7 but in your example it is choice 3?

Perhaps I do not understand the logic correctly.
Can you please elaborate or check if your example is correct all the way down?
Thx

Can't seem to attach a file
Try entering the following in H2, say, and pull down as needed

Please Login or Register  to view this content.

Hi

Yes the file is correct. I've updated the attachment and I've now included the household number, maybe to make the story more clear.

Basically the model that I've estimated in SAS predicts in week 3 household 15 (from A16 to A22) will buy product 7 (0,534353 is the highest probability in that week). However the real choice of household 15 was product 3. In this case the model thus predicted the choice wrong.

If we start from the beginning:
So for household 1 in week 5 (from A2 to A8) the model predicts that alternative 7 will be chosen (0,534353 is the highest) and indeed houshehold 1 has bought product 7 (choice dummy has value 1 for product 7). Thus in this week the model has a "hit", meaning it predicted it right.

For household 1 in week 6 (from A9 to A 15), the model also has a hit.

However for household 15 in week 3 (from A16 to A22), like you said, the predicted choice and the real choice don't match. The model thus predicts it wrong.


The dummy I need to create will have following values:
= 0 for row 2 until 7
= 1 for row 8 (because here the model has a hit)

= 0 for row 9 and row 11 until 15
=1 for row 10 (because here the model also has a hit)

= 0 for row 16 until row 22 (because here the model misses, it predicts product 7 but the real choice was 3)

....


I hope this makes my problem a bit more clear
Thanks for your help!!
It's much appreciated!

I think the provided formula might give you a start? Have to leave now.
Veel geluk

I have attached a file with the results. The last column shows if there is a correspondence between prediction or not
To determine your percentage of wrong choices count the nr of nok divided by the total number of rows itself divided by 7

Hello,

The formula that Pepe Le Mokko suggested is working for me

Please Login or Register  to view this content.

Edit: This is not an array formula and working as it is.

Thanks for your approval, but this is NOT an array formula, it works as is - See post #8
INDEX returns a cell reference so the formula boils down to =--MAX(cell_ref_1:cell_ref_2)=A2

You are right, I entered the formula normally and it still works.
I'm sorry for pointing out something that is not right. I will edit the post above to avoid misunderstand.

Np. The best way to see it at work is to use the Evaluate Formula tool

Hey thanks for helping

But Pepe the document you posted still isn't correct unfortunately.

For example if you look at row 22, there the new dummy gives a 1 but this should be a zero. The model predicted that product 7 will be chosen yes, but the consumer chose product 3 (see the choice dummy in row 18). So in that week the dummy should only contain zero's since the choice was predicted wrong.

So the newly created dummy CAN only be 1 if also the choise dummy in row F is 1. And it must have the value 1 if this 1 of the choice dummy corresponds to the highest probability of that week.

Do you understand it?

Thanks for your help!

(btw I have the 2011 Mac Excell version (Dutch), should that be a problem then I can always find another computer because I will have to use this formula also on other models I've estimated)

Perhaps use replace the formula in column I with =H2*F2 and pull down as needed You will now only get a 1 when the choice is correct

Yes that is it. Then multiplying it with the choice dummy will yield 1 (model answers same as consumer) or 0

To see the formula at work use the "Evaluate formula" tool and to understand better what the INDEX function really does read http://www.excelhero.com/blog/2011/0...ing-index.html

Graag gedaan