Specifying a cost based on price ranges

```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

```
 0
Andy
3/25/2010 6:19:18 PM
excel 39879 articles. 2 followers.

3 Replies
652 Views

Similar Articles

[PageSpeed] 44

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Andy Roberts" <andy@blue-bean.co.uk> wrote in message
news:u\$YqweEzKHA.928@TK2MSFTNGP05.phx.gbl...
>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
>

```
 0
Don
3/25/2010 7:07:21 PM
```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" <dguillett1@gmail.com> wrote in message
news:OxTIn5EzKHA.5288@TK2MSFTNGP05.phx.gbl...
> change to suit
> =LOOKUP(D2,{1,5,9,12;1,2,3,4})+IF(D3="d",100,"")
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Andy Roberts" <andy@blue-bean.co.uk> wrote in message
> news:u\$YqweEzKHA.928@TK2MSFTNGP05.phx.gbl...
>>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
>>
>

```
 0
Andy
3/25/2010 8:15:19 PM
```      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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Andy Roberts" <andy@blue-bean.co.uk> wrote in message
news:uETpmfFzKHA.1236@TK2MSFTNGP06.phx.gbl...
> 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" <dguillett1@gmail.com> wrote in message
> news:OxTIn5EzKHA.5288@TK2MSFTNGP05.phx.gbl...
>> change to suit
>> =LOOKUP(D2,{1,5,9,12;1,2,3,4})+IF(D3="d",100,"")
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Andy Roberts" <andy@blue-bean.co.uk> wrote in message
>> news:u\$YqweEzKHA.928@TK2MSFTNGP05.phx.gbl...
>>>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
>>>
>>
>
>

```
 0
Don
3/25/2010 9:39:56 PM

Similar Artilces:

Vary Color based on value
Is there a way in a bar chart to have a bar be a specific color if less than, lets say "5" ? Instead of manually changing each bar that is less than a specific number. Hi, Create two additonal columns of formulas, suppose your original data is in B2:B12, then in C2 enter the formula =IF(B2<=5,B2,0) in cell D2 enter the formula =IF(B2>5,B2,0) Copy these formulas down. Plot these ranges instead of your original range. Format one series one color the other another. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Jennifer" wrote: >...

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

base currency
The currency in Turkey will be changed next year by dividing the existant currency by 1.000.000. How can I divide the amount of all of my transactions by 1.000.000 ? ...

Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have no use for all of the rows at the same time and have to keep hiding and showing them as need arises. Is there a macro to hide chunks of it based on the value of one cell of the row? In other words, en each row I will have a formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to hide the row or not. I tried case.select but it takes a LONG time and I would have to write a piece of code for every line. FYI, the rows that need hiding will be in sequence, in other words, fro...

Add non-inventory item in MO Reciept entry (Unit Cost)
Dear All, I am using the (Add Component) button on the MO Reciept entry screen to add some non-inventory items, I notice that the decimal places for the unit cost is (2) although my Functional currency is (3). Kindly advice for the reason behind this, or if there is any setup required for that. Thanks Dear, Go to Tools --> Setup --> Purchasing --> Purchase Order Processing Find Decimal Places for Non-inventoried Items, click on the Expantion Button near to the Currency Field and set the decimal places for you curreny. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS S...

SUM cells in column based upon criteria in two others.
Can not seem to put together a formula that accomplishes the following: Column J contains contributions by individuals for 2010 Column I contains contributions by individuals made in 2009 Column N contains an indicator (+ - or 0) of the increase or decrease over the two years Column P contains a designation of which members are new members. I need a formula that looks at column I cells against column J cells, totals the contributions made be each of the criteria in column N, but does NOT include those with any designation in column P. Example of the data: I ...

How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column to a row? I imagine it should be very simple, but I haven't found out how yet. For instance: row# 1 data 1 2 data 2 3 data 3 4 data 4 to column# 1 2 3 4 data1 data2 data3 data4 ...

Cannot open Price Sheet
Hi All, We encounter an error message that says "This record is currently in use by another user" when trying to open a Price Sheet although no one is using such record. Kindly advise how to fix this problem. Thank you. Hi All, I already found the solution. I cleared the tables in Dynamics: ACTIVITY SY00800 SY00801 And in TempDb: DEX_LOCK DEX_SESSION -- Yani "Yani" wrote: > Hi All, > > We encounter an error message that says "This record is currently in use by > another user" when trying t...

Return Range Based on Cell Value
This topic has been covered in varying degrees, but the problem is that I do not want to sum or count the range. I want the range itself returned as the value. For instance in A1 is 7. i.e. July In B1-B12 are dates 201101, 201102, etc. I need a formula that will count out the cells and return the range based on that value in A1. In this case it would return B1:B7. This of course would be nested in another formula. On Tue, 4 Oct 2011 17:18:20 -0700 (PDT), Pablo < > wrote: >This topic has been covered in varying degrees, but the problem is >that I do not want to sum or count the...

can i create formula giving totals based on financial & text info
Am i able to create a formula that gives me monetray totals for expenditure on hotels, split into totals for 6 varying business sectors? ...

Portfolio stock prices
Anyone else having problems today with Canadian Stock prices in Money 2005 Canadian Standard Edition. All my prices are 1000 times the actual stock price, makes me feel like Bill Gates. Yes, I am having the same problem today. Thanks, Mark "Michael Brimicombe" wrote: > Anyone else having problems today with Canadian Stock prices in Money 2005 > Canadian Standard Edition. All my prices are 1000 times the actual stock > price, makes me feel like Bill Gates. > > > Just checked again, prices seem to be correct now. "Mark - Canada" <Mark -...

T/F Can't set Hyperlink base from within macro
Excel 2000. I wanted to set the Hyperlink base from within a macro. Then I read: "BuiltinDocumentProperties Property Returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only." Among the listed built-in properties is "Hyperlink Base". Putting that together with "read only", I deduce that although I can set the Hyperlink Base as a user, this cannot be done via a macro. Am I correct or is there a loophole somewhere that would allow me to do this? Chris Beall Hi Chris: Sub chris3() ...

Factors governing Inventory Cost Calculation at dealership
Homework? -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Shankar Raman" <Shankar Raman@discussions.microsoft.com> wrote in message news:E05B70B8-4AC0-415D-82DE-CDFD450832BF@microsoft.com... > ...

special pricing
I am trying to figure out how to set special pricing for specific customers. I already have retail, A & B pricing, with A being lowest, then B, and retail being the highest price. I offer the different prices based on volume. Now, I am trying to set up a customer with special prices on a select number of items. these prices are below A pricing, and i have been changing the pricing on each transaction. this is very time consuming, and prone to making mistakes. i would like to be able to set up a special pricing structure for specific customers on a job by job basis, or for a set per...

pivot table with dynamic base data
hiya, im trying to create a pivot table where I can change my base data (inserting or deleting rows of data) and still have this reflected in the pivot table. My solution was to use entire columns as my range for input to the pivotTable, but this means including blank rows which seems to muck up the calculations. I cant think how else i can allow for new rows to be added to the base data anyone got any ideas how to do this? thanks loads for your help! You can use a dynamic range as the source. There are instructions here: http://www.contextures.com/xlPivot01.html bb wrote: > hiy...

I have recently updated my computer to VISTA and have had to re-instal Money2002 Personal and Business edition. Since then the online price updates no longer run on start up. I have to manually use the update price option and this does retrieve updated prices for the shares as expected. Does anyone kn ow how to fix the problem so that the update of prices happens automatically every 6 hours as it used to do when I was using Windos XP Home? Thanks in antcipation of getting a resolution to this problem. ...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

Multiple time ranges
I am trying to create a formula that will look up the time values and return a letter. Times are in column F. Must include seconds (as values does include seconds). 07:00:00 - 17:30:00 = "A" 17:31:00 - 00:30:00 = "B" 00:31:00 - 06:59:00 = "C" <as values does include seconds).> But you miss out whole minutes in your specications. Anyway, this should work, but do check your threshold values. Maybe you need to add a second or less than a second. Set up a table like this (in this example: A1:B4) 0:00:00 B 0:30:00 C 7:00:00 A 17:30:00 B With your time to l...

How to Drag/Move the IE control based Application
Hi All: I am developing Dialog based application. I have placed Microsoft WebBrowser control(IE control) on main Dialog of application. On the IE control, all messages are being passed to IE control. Now problem is that I am unable to drag/move this IE Control/Dialog. Any idea about moving IE control/Dialog Please help me in this regard. Regards, Ghazanfar Ali ...

how to define range names
how do name cells so that I can see what the formula is calculating? Such as A1 * B1 = Final Price where A1 is price and B1 is discount so it reads in the final price cell as price * discount thanks in advance, A One quick way is to use the namebox (the droplist box just to the left of the "=" sign) In Sheet1, say: Select A1, then click inside the namebox, and type the name: price, then press ENTER Repeat to name cell B1 as: discount Then we could put in say C1: = price*discount The other (pe...

Bug: printing of a specific page range is broken in MFC-VS2005
If you create an MFC application that includes printing support, and your application does print-time pagination as described in this MSDN library topic: http://msdn2.microsoft.com/en-us/library/w7wzay73(VS.80).aspx then specifying printing of a specific range of pages in the print dialog does not work. The application always prints all pages of the document. To see this problem download and build the WordPad sample MFC application. Start a new blank document, then paste into it enough text to fill several pages. Choose Print from the File menu. Choose a print range of page 1 to page 1....

no permission to send the message for the specified user
-- Barbara How do I solve this? You do not have permission to send the message on behalf of the specified user. I am trying to forward faxes from Outlook. You can't use forward with faxes. You have to Open the fax then Print it to your fax printer and generate a new fax. -- Russ Valentine [MVP-Outlook] "Barbara" <Barbara@discussions.microsoft.com> wrote in message news:6D5A4306-09CD-44BB-90C4-31F93E7A5618@microsoft.com... > > -- > Barbara > How do I solve this? You do not have permission to send the message on > behalf of the specified user. ...

ActX as base?
Hello all, I have an Act X control that I'm using but would like to replicate it and add a few more functions. Can an Act X control be used as a base class, and if so, how? I tried to replacing the 'COleControl' with my 'CAXBaseCtrl' in a new Act X's ctl.h file, but blew up with a lot of "...unresolved external symbol..", so maybe it can't be done? TIA, Ray K. ...

Canon Rebel Price
Canon LP-E5 Battery Pack for Canon Digital Rebel XS &amp; XSi Digital SLR Camera Price:\$106.16 Image: http://bestdealfinder.us/image.php?id=B0015GADB4 Best deal: http://bestdealfinder.us/index.php?id=B0015GADB4 Canon NB-2LH Rechargeable Battery Pack for Rebel XT/XTi Digital SLR Cameras and VIXIA HV Series and ZR Series Camcorders Price:\$70.00 Image: http://bestdealfinder.us/image.php?id=B0002YE686 Best deal: http://bestdealfinder.us/index.php?id=B0002YE686 Canon XS/XSI Digital Rebel Kit with Case, 58mm UV Filter and LP-E5 Li-ion Battery Price:\$99.99 Image: http://bestdealfinder.us/im...

how do i work out what price to charge
i am a nail technician, and would like help with working out what i spend every month( on supplies e.g nails, nail polish , nail polish remover, fuel( car) , etc etc ), then be able to have a total figure at the end where i charge for a certain job, i.e french nails \$30... by being able to work out how much im spending a month, compared to how much im earning... i'll soon realise whether or not im under charging myself look forward to your response kind regards Good questions. You could start with a simple table, say with 5 columns: Date, Category, Description, Expense, Revenue....