Add an amount to a cell if the value of one of the conditions is greater than X

[execlass2]

OK, sorry to test your patience again.

So far I have come to this formula which is working:

=MAX(CEILING (((E3*$AG$2*$AD$2)+(F3*2))*$AF$2;5);50)

In this case:

E3 is the number of kms
$AG$2 is the return kms factor
$AD$2 is the cost per mile

F3 is the cost of tolls (x 2 for the return trip)

$AF$2 is the cost of VAT TAX

5 is the round up value.

50 is the minimum price

Now, I want to add the following condition: If a trip (considering the return) exceeds X number of kms, the cost of a driver's meal must be included.

In this case, if a one way trip (E3 cell) exceeds 275 kms, then the cost in the $AH$2 cell must be added to the calculation BEFORE VAT. If the one way trip is less than 275 kms, then no cost (or zero cost) should be added.

I assume this involves the use of the IF function, but, can you help with this?

Thank you.

[AliGW]

So how would you calculate the length of the journey? Is it E3+AG2?

Whatever it is, try this:

=MAX(CEILING (((E3*$AG$2*$AD$2)+IF(Total_km <= 275; 0; AH2)+(F3*2))*$AF$2;5);50)

[execlass2]

[QUOTE=AliGW;5084839]So how would you calculate the length of the journey? Is it E3+AG2?

Dear Ali,

The length of the journey is calculated by E3 * $AG$2 (in this case, the value of AG2 is 2,02, in which 2 represents the return trip and ,02 is an average of extra kms from the town center to any given site in the surroundings)

In any case, the value of E3 is enough for the calculation. If E3 exceeds 275 kms (thus, a total return trip of 555,5 kms, which means a trip with more than 4 1/2 hours), then add the value of a meal in $AH$2. If less than 275, then add zero (or do nothing).

[AliGW]

So this then:

=MAX(CEILING (((E3*$AG$2*$AD$2)+IF((E3*$AG$2) <= 275; 0; AH2)+(F3*2))*$AF$2;5);50)

[execlass2]

Dear Ali,

It works! I had to modify it a little:

=MAX(CEILING (((E3*$AG$2*$AD$2)+IF((E3*$AG$2) <= 550; 0; $AH$2)+(F3*2))*$AF$2;5);50)

But yeah, it's working!

Thank you so much You rock.

[AliGW]

Great!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.