=(excel)*(help)/(reqd)

Discussion in 'The Front Room' started by Lester, Feb 8, 2004.

  1. Lester

    Lester One of Freddy's beloved

    Hope someone here can help.

    I'm trying to create a pricing tool for (insert large British monopolistic telecommunications organisation here) private circuits - basically on some products you pay for each end connection and then you pay a set price per metre or kilometre for the main link in between, which changes after a certain threshold. EG;

    If site A and site B are 26 kilometres apart, you pay £3500 per end, then £310 per kilometre for the first 15 kilometres and £105 per kilometre for the subsequent ones.

    There are various products that have different criteria and I am trying to come up with a way of pricing the same 2 postcodes (sites) using different product, quickly on the same spreadsheet.

    IE:

    sheet one would be the cover sheet with the postcode info and the main link distance.
    sheet two would calulate the main link distance charge based on the above criteria (15km X £310 + 11km x 105)
    sheet three would be for a different circuit product with a different main link charge scale and so on.

    The problem is what is the calculation in the main link cell? You have get the cell to calulate the first portion of the main link distance at a certain rate and the next portion at the next rate. It has to be done on a cell reference basis in case of price changes and also to enable multiple site to site quotes.


    Alternatively someone who works for said organisation could send me the proper tool. :)

    Any help appreciated.
     
  2. Lupaerian

    Lupaerian Fledgling Freddie

  3. dysfunction

    dysfunction FH is my second home

    Is this what you are looking for?

    This will give you a cost of 9305 using the values you gave in your example.



    =+A4+(D3*C3)+((B9-C3)*+D4)

    A4=3500 (intial Cost)
    D3=310 (price for first 15 kms)
    C3= 15 (the 15 kms)
    B9= 26 (distance between two points)
    D4= 105 (cost for for every km over the first 15kms)
     
  4. Chameleon

    Chameleon Fledgling Freddie

    Excel not Excal. Your obsessed man! :p
     
  5. Lester

    Lester One of Freddy's beloved

    Thanks Dys - but what happens if the distance is less than 15 km - you then get a minus value!
    I've kinda sorted it but it's messy - lot's of IFs etc.

    Cheers
     
  6. dysfunction

    dysfunction FH is my second home

    I didnt think of it being less than 15kms...

    You would only need 1 "if" statement...

    If(b9<c3,(+A4+(D3*C3)),(+A4+(D3*C3)+((B9-C3)*+D4)))
     
  7. Wij

    Wij I am a FH squatter FH Subscriber

    looks like l33t speak to me :(
     
  8. ~Yuckfou~

    ~Yuckfou~ Lovely person




    Hablas Ingles?
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.