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

Lester

One of Freddy's beloved
Joined
Dec 22, 2003
Messages
468
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.
 

dysfunction

FH is my second home
Joined
Dec 22, 2003
Messages
9,709
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)
 

Lester

One of Freddy's beloved
Joined
Dec 22, 2003
Messages
468
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
 

dysfunction

FH is my second home
Joined
Dec 22, 2003
Messages
9,709
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)))
 

Wij

I am a FH squatter
Joined
Dec 23, 2003
Messages
18,185
dysfunction said:
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)))

looks like l33t speak to me :(
 

~Yuckfou~

Lovely person
Joined
Dec 22, 2003
Messages
2,594
dysfunction said:
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)))




Hablas Ingles?
 

Users who are viewing this thread

Top Bottom