• 462K Members
• 5,906 Online
• 559K Conversations

## Formula in Excel

Occasional Visitor

# Formula in Excel

I know the basics in creating some formulas I'm trying to create a spread sheet for mileage that if someone has up to 1500km it is charged at a specific rate ,  and in another column I want anything above 1500 charge at a separate rate can someone please provide some ideas on how to create those formulas

3 Replies

# Re: Formula in Excel

Perhaps, you need a formula like this:
=IF(A1>1500,
A1*25,
A1*20)

# Re: Formula in Excel

For the data structured like this

if in B1 is your rate for the mileage below 1500, and in C1 - above it, you may use formulas

in B2:
=MIN(\$A2,1500)*\$B\$1

in C2:
=MAX(0,\$A2-1500)*\$C\$1

in D2:
=B2+C2

and drag them down till end of your range

# Re: Formula in Excel

If the charge per km for the first 1500 km is 20 and the charge per km for the excess over 1500 km is 25, such would be similar to a tax table wherein the formula, assuming the distance is in A6, would be:
=LOOKUP(A6,
{0;1500},
{0,30000}+(A6-{0;1500})*{20,25})
Instead of the array constants, cell references should be used, such that {0;1500} should be entered in, and referred to as, B\$2:B\$3, {0;30000} should be entered in, and referred to as, C\$2:C\$3, and {20;25} should be entered in, and referred to as, D\$2:D\$3.
Thus, the same formula using cell references instead of array constants would be:
=LOOKUP(A6,
B\$2:B\$3,
C\$2:C\$3+(A6-B\$2:B\$3)*D\$2:D\$3)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies