Need help with formulas for creating a spreadsheet to help me determine the best rates for my client

[TEGAR]

I work as a sales rep for the Kansas City STAR. We've just introduced a
new rate structure that has a great deal of variables to determine the
best rate for my client. (If our advertisers play by all the rules
their rates are sometimes cut by more than half.)

It seems to me that there should be a way to create a spreadsheet that
by entering the date(s), size and section the advertiser wishes to run
their ads it would give the correct pricing for each ad.

Our rates are based on the number of ads ran within a seven day window
and the section of the paper they run in. We special discounts if the
advertiser will run the same ad for multiple days or in certain areas
of our paper.

I envision a spreadsheet where I could enter something like this;

Client Name or Account Number; John Doe, 12345678

Ad Size, Color, Etc.; 3 column by 10 inch, B&W, etc.

Date when the ads are to start running; SUN, May 27

The days of the week and the section each ad will run;
SUN - Main News
MON - Sports
TUE - Metro (and so on)

The spreadsheet from this information would know what pricing level the
advertiser is qualified for based on the investment level they've
committed to. It can then determine the best rates for the advertiser
and populate a worksheet that would fill in product codes and other
variables the production staff needs to properly place the ad in the
correct publication and section.

Here is a link to our rate card that may better explain how this might
work. Any help or advise someone could provide would be greatly
appreciated.

Please feel free to contact me and I can go into greater detail.


http://www.kansascity.com/multimedia/kansascity/kansascitystar/archive/RateCard/RetailRatesROP.pdf

 

[JE McGimpsey]

Here is a link to our rate card that may better explain how this might
work. Any help or advise someone could provide would be greatly
appreciated.

The basic solution is rather straightforward: use VLOOKUP() to determine
the price from the appropriate table.

However, it appears to me that the business logic used in determining
which table of your rate card gives the best rate is rather complex. It
certainly can be duplicated in XL, but frankly, I didn't invest the time
to try to sort it out.

If you want to break the problem down into smaller component parts, we
could probably help you to build it.