Tables

**Jennifaa** · 10-26-2011, 06:06 PM

This attachment has nothing to do with a business or anything personal. Excel 2010

Problems with the attachment:

G8 - =INDEX(A$26:B$28,MATCH([@Dept],A$26:A$28,0),2)*[@Sales]

This formula is supposed to give me an answer, but instead it is giving me a dash. I have tired to break down this formula and whatever I'm doing is not working at all. It is supposed to show 80,685 inside of cell G8, and then after the dash is replaced with it the total for commissions should equal 4,854,115.

Rate in cell B26 to B29 should also have answers when the formula in G8 is fixed.

If this message needs to be deleted I will do so.

**tlafferty** · 10-26-2011, 06:14 PM

Your formula only needed to be adjusted down one row to include trade. Also, the reason it returned a dash is there were no values entered in the lookup table. See attached, and you'll want to put actual values for the rates.

**Jennifaa** · 10-26-2011, 06:25 PM

Cell G21 is supposed to answer 4,854,115 (sum )
and Cell D21 is supposed to answer 39.07692308 (Average)

Is there a reason as to why they are not equaling what they should?

Cell G8 should be 80,685 instead of what is it now to equal 4,854,115

**tlafferty** · 10-26-2011, 06:44 PM

I just used some random rates in the rate table in your sheet. Using the figure you gave me, and the goal seek tool, you will find that the rate for K-12 should be 0.075 or 7.5%. If you know what the figure should be for cells G9 and G11, you can use goal seek to determine rates for higher ed and trade.

On a side note, I find it odd that you know what the results of the calulations should be without knowing what the rates are... is this homework of some kind

?

**Jennifaa** · 10-26-2011, 07:02 PM

haha, yeah you can call it that ^^;

Funny thing about certain professors is that sometimes they are not always that helpful, and make you feel stupid in the end.

**tlafferty** · 10-26-2011, 07:26 PM

Hmmmm - while not explicitly against the forum rules as I read them, I'm not sure how ethical this is. Rather than directly giving you the answers to this, I'd be more comfortable giving you guidance. I would be willing to bet that the outcome for you as a learner would be better if you had a bit of guidance toward the goal and struggled a bit to understand.

In the meantime, did what I suggest help?