1

I am wondering of you would be able to help me with a nested IF function problem I am having. Here is the question: (it's a long one sorry!)

  • If the freight type is Victor Von Doom Freight then the freight cost is calculated at $4.50 per kilogram of the item weight and a quarter of a cent ($0.0025) per kilometer (distance) if over 1,000km per item ordered (Order Qty).

  • If the freight type is Otto Octavis Transport then the freight cost is $10 for the first 1.5 kilograms and then $2.50 per kilogram over 1.5kg per item ordered (Order Qty).

**I also must include ROUNDING to 2 decimal places

So basically I have my IF statement as follows:

IF(FreightType="Victor Von Doom Freight", ROUND((4.5*ItemWeight+IF(Distance>1000,0.0025*Distance,"0"))*OrderQty,2), IF(FreightType="Otto Octavius Transport", ROUND((10+IF(ItemWeight>1.5, (ItemWeight-1.5)*2.5,"0"))*OrderQty,2),"0")

1

Your code currently is incomplete. If you reformat it you can also write it like this:

IF
(
    FreightType="Victor Von Doom Freight"
    ,ROUND
        (
            (
                4.5*ItemWeight + 
                IF
                (
                    Distance>1000
                    ,0.0025*Distance
                    ,"0"
                )
            )
            *OrderQty
            ,2
        )
    ,IF
        (
            FreightType="Otto Octavius Transport"
            ,ROUND
                (
                    (
                        10+
                        IF
                        (
                            ItemWeight>1.5
                            ,(ItemWeight-1.5)*2.5
                            ,"0"
                        )
                    )
                    *OrderQty
                    ,2
                )
            ,"0"
        )

Which shows you're missing a closing bracket. Just adding one closing bracket to the end of it should make it work.

3
  • i only realised you could enter line breaks and spaces into a formula a few weeks back, after spending about 4 years using excel ! – PeterH Sep 28 '17 at 14:42
  • I got this to work, although I found it a lot less confusing to just have all the data for the feright costs in a seperate table and referacne those cells. Also means it's easier to make edits if their shipping charges change. – O.Allen Sep 28 '17 at 15:13
  • @User91504 Personally I'd probably not use them within Excel but if you're trying to debug it, it can be rather nice to figure out whenever your brackets are correct. – Seth Sep 29 '17 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.