Applying a Tiered tariff to calculation

[akhilnairwork]

Hi Everyone,

My first post here and i hope this is done right. PLease note i have an issue where i have varioud vedors having different tariff based on day range. I have inserted sample Data avaiable and the tariff.

in the file i also included expected result . Hope someone can help me understnd how to write a formula to look at all the parameters.
1) Carrier Name
2) Container Type
3) Free time
4) Tariff to be selected
5) calculated total USD

so

PLEASE HELPW

[XLent]

The below would, I believe, replicate your results:

Please Login or Register  to view this content.

[akhilnairwork]

Hi There XLent, thank you !!! it seems to work, though am wondering if i have more criteria what would i need to add / ammend?

[XLent]

If, by criteria, you're referring to conditions that determine appropriate rate then the answer is to add appropriate tests within the AGGREGATE.

In basic terms the AGGREGATE is finding the last row in which all criteria are met using a construct of

ROW(range) / (boolean test 1) / (boolean test 2) / (boolean test 3)

In native XL Booleans, when coerced via a mathematical operator, equate to either 1 (True) or 0 (False)

Given the above it follows that if any one of the Boolean tests / conditions return False then the resulting value from above calc will, instead of a row number, be #DIV/0!
note: the 2nd parameter of the AGGREGATE [6] tells XL to ignore the errors... so it only looks at results where the result was valid, i.e. rows in which a valid result was found

The 1st parameter of the AGGREGATE [14] tells XL to apply a LARGE call to the resulting array of values whilst the final parameter [1] tells XL to use the Largest value (i.e. k)
so, if you used 2 it would return 2nd largest etc)

This approach works here because your rates are listed in ascending order, and you want the last found that satisfies all conditions (which would be in the 'largest' row number)

the outer INDEX simply uses the result of the AGGREGATE to return the associated value - so if AGGREGATE returned 21 the INDEX would return contents of P21 etc

[akhilnairwork]

Thanks So Much i think i have gotten the first one to work.

However using the above i struggle with how to apply this if the tariff changes. For example i waiting charge per hour. XX and then if it hits 24 hours the amount becomes a day charge

Attached is what i am now trying to solve but i cannot seem to get the aggregate to work at all. (Attached file) Need to solve for cell B2 and then drag down hopefully

Hopeful you can advise me of this. Much Appreciated!

[Ghozi Alkatiri]

at B2
=MOD(A2,24)*7+TRUNC(A2/24)*100
copy down