Formula to figure ad schedules

G

gravity003

I need to set up a spreadsheet showing the different rates for parts of a page.

Rates 1/8 page=$550, 1/4 page=$800, 1/2page=$1,000 and full page=$1500 with
a 25% premium for the months of Sept, Oct, and Nov.


I want to be able in insert the page fraction purchased and in a column
below give me the dollars amount?

Spreadsheet would look like this?

Vendor Name Jan Feb March April May June Ect
XYZ 1/8 1/4 1 0 0 0

550 800 1500 0 0 0
 
B

Biff

Hi!

You'd have to format the cells where you enter the page size as a FRACTION
(up to one digit)

Then:

=IF(B2=0,"",LOOKUP(B2,{0.125,550;0.25,800;0.5,1000;1,1500})*IF(OR(B1={"Sept","Oct","NOV"}),1.25,1))

Biff
 
G

gravity003

Everything works except for the premium rate of 25% for Sept, Oct and Nov.
Thanks for your help. Now if I or any one else figure out how to do the
premium rate.
 
B

Biff

Everything works except for the premium rate of 25% for Sept, Oct and Nov.

Explain what that means?

Biff
 
G

gravity003

We charge the base rate plus 25% if the vendor runs an ad in the months of
Sept, Oct and Nov. The formula you gave me enters the base figure of (1/8
page $550 and not the correct rate of 687.50 for these months) and does give
the premium rate.
 
G

gravity003

Yes I do not why it is not doing it correctly. I can see on your example it
is working correctly. I think you for your help. I will change the logic
for Sept, Oct and Nov and it now gives the correct amount. Most be something
in the different versions of exel.

I can now lay out the report and again think for your assistance. You will
save me hours of manually entering data.
 
Top