Nested if

[Joym]

Capture.JPG

please refer to image for better understanding

I have 4 columns Total Amount(E2) and Tax Code(F2) Customs and Sales Tax Rate (g2)
There are 2 tax codes 1 and 2
i want to calculate the Customs and Sales Tax Rate using this criteria

if Tax code ($f3) = 1 and Value for total Amount($e3) in the equal to or greater then than value in cell $M$5 and equal to or less then the Value in cell $N$5 if yes then $e3 multiply by $0$5
Else if Tax code ($f3) = 1 and Value for total Amount($e3) in the equal to or greater then than value in cell $M$4 and equal to or less then the Value in cell $N$4 if yes then $e3 multiply by $0$4
Else if Tax code ($f3) = 2 and Value for total Amount($e3) in the equal to or greater then than value in cell $M$11 and equal to or less then the Value in cell $N$11 if yes then $e3 multiply by $0$11
Else if Tax code ($f3) = 2 and Value for total Amount($e3) in the equal to or greater then than value in cell $M$10 and equal to or less then the Value in cell $N$10 if yes then $e3 multiply by $0$10
Else if Tax code ($f3) = 2 and Value for total Amount($e3) in the equal to or greater then than value in cell $M$9 and equal to or less then the Value in cell $N$9 if yes then $e3 multiply by $0$9

i tried this just to test however didnt work. i tried a few combinations was unsuccessful
=if($f3=1 And($E3=>$M$5, $ $E3=>$N$5),$E3*$O$5,if($f3=1 And($E3=>$M$4, $ $E3=>$N$4),$E3*$O$4))

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook (not a pic). The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Joym]

Use a Nested IF function to calculate the customs and sales tax rate to be added on each company
contract based on the Total Cost of the imported goods and his Tax Status (A Tax status of 2 means
the company enjoys preferential custom and sales tax treatment where as a Tax status of 1 implies
non-preferential custom and sales tax treatment). This formula should use relative or absolute
references where appropriate. (10 marks)

[davsth]

Perhaps in G3

Please Login or Register  to view this content.

Regards

Dav

[FlameRetired]

If you are open to an alternative to nested IF INDEX/MATCH could work here. This approach is not a common one. There is a seldom used 4th argument in INDEX called area_num. It works well in situations like this where there are multiple lookup tables. It permits selection of the relevant table (area_num) through the Tax Status codes 1,2. The row and column arguments then pertain only to that area.

The explanation is more complicated than the actual usage.

In Name Manager find the non-contiguous range named 'Status'. =Sheet1!$M$9:$O$11,Sheet1!$M$4:$O$5

Note that each area is comma separated. They are numbered left to right 1, 2 ... n. In this case there are of course only 1 and 2.

(Note also that Sheet1!$M$9:$O$11,Sheet1!$M$4:$O$5 can be directly entered in the first argument of INDEX however the entire set must be enclosed in () or you will get a "too many arguments" error.)

The formula in G3 and filled down is

Please Login or Register  to view this content.

Try it and if you have questions about it feel free to ask. Once understood I believe you'll find it is simpler than nested IF.

Does this help?