SOLVED

# Formula Required

Copper Contributor

# Formula Required

Hi All,

I need a formula for the following see attached Sheet.

Brief

Starting at 50klm distance the base rate is \$1.50

Every 50klm more I want the rate to increase \$0.25

I have it set up so when you enter the Distance (KLM) (sourced via Google Search)

The Quantity needs to rounds up to the next whole number according to Distance(KLM) entered

The rate increase needs to corresponds with Quantity total.

Any help would be appreciated. Thank you

5 Replies

# Re: Formula Required

@pjukes That file requires a "sign-in". Please provide a link to the file on Onedrive, Dropbox or similar, giving access without the need to sign in.

# Re: Formula Required

Try this one.
best response confirmed by pjukes (Copper Contributor)
Solution

# Re: Formula Required

@pjukes Yes! That one works. But, oh my, how colorful and an almost abusive use of conditional formatting. You really want all of that?

Anyhow, enter the formula below in E2 and I believe it does what you ask for.

``=1.5+(INT([@[DISTANCE (KLM)]]/50)-1)*0.25``

# Re: Formula Required

Hahaha, Yes, slight overkill I know. I,ll try that out and let you know how I go. Thanks

# Re: Formula Required

Thank you! that worked perfectly.
Very much appreciated.
1 best response

Accepted Solutions
best response confirmed by pjukes (Copper Contributor)
Solution

# Re: Formula Required

@pjukes Yes! That one works. But, oh my, how colorful and an almost abusive use of conditional formatting. You really want all of that?

Anyhow, enter the formula below in E2 and I believe it does what you ask for.

``=1.5+(INT([@[DISTANCE (KLM)]]/50)-1)*0.25``