Specifying a cost based on price ranges

[Andy Roberts]

I have a schedule of rates which are fixed for a number of sites and whether
the sites are local or further afield.

For example

Rate for 1-4 sites (local) £500
Rate for 1-4 sites(Distance) £600

Rate for 5-8 sites (local) £900
Rate for 5-8 sites(Distance) £1000

Rate for 9-12 sites (local) £1300
Rate for 9-12 sites(Distance) £1400

Rate for >12 sites (local) £1600
Rate for >12 sites(Distance) £1700

I have a spreadsheet which has each site as a separate row and I want to
calculate a cost for each site depending on whether it is part of a group of
say 4 or 8 etc. The cost for one site would be the same as 4 sites etc.

Each site has a column which has a number indicating how many sites are in
the group as a whole and another column with either "L" for local or "D" for
distance.

Can anyone advise me on the formula I would use to provide a cost against
each site? I'd also like to hold the rates outside of the formula so I can
alter them if necessary (say increase our rates by 10%)


--
Regards

Andy

Andy Roberts
Win XP, Office 2007

 

[Don Guillett]

change to suit
=LOOKUP(D2,{1,5,9,12;1,2,3,4})+IF(D3="d",100,"")

 

[Andy Roberts]

Don

Ive never used LookUP, but I presume D2 is a cell refernce which contains
the number of sites, The numbers represent the range changes. Not sure what
the 1,2,3,4 represents and I presume D3 is the cell which indicates whether
the site is local or distance and the 100 is the price difference to be
added id the sites are distance?

What if the difference between rates wasn't a constant £100 and each range
differed?

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.