Hi all experts, i have a Q about using the "if" compound statement. my case is as follows (let's see if i can explain this issue clearly):
i have a worksheet that compares LEASING vs PURCHASING for trailers for my company. i have an annual chart (12 months) for inputting monthly leases of trailers. i also have a chart that calculates depreciation for the trailers if we buy the trailers. To compare apple to apple in terms of time period, the depreciation have to start AT THE SAME TIME as the lease starts.
i tried to use a "IF" statement to do the calculateion for the depreciation. the formula i created is as the follows:
"=IF(D46>0,C46/10/12,IF(E46>0,C46/10/11,IF(F46>0,C46/10/10,IF(G46>0,C46/10/9,IF(H46>0,C46/10/8,IF(H46>0,C46/10/7,IF(I46>0,C46/10/6,IF(J46>0,C46/10/5,0))))))))"
basically i asked the computer to check that in each month, if there's a value greater than zero, calculates the monthly depreciation times the number of months remains in the year. for example, if there's a lease amount in april, then it will calculates the depreciation as monthly depreciation times 9 [there are 9 months left in the year if we start to count from April (with April included.)]
the above formula that i created work BUT i can only have 9 "IF"s compounded (in a way that an "IF" is imbeded in another "IF"...) is there a limit of compounding like this? i can only go up to September but i need a full year, which means, i need it to be imbeded 12 times!
i know this looks long... but PLEASE HELP!
Tracy
i have a worksheet that compares LEASING vs PURCHASING for trailers for my company. i have an annual chart (12 months) for inputting monthly leases of trailers. i also have a chart that calculates depreciation for the trailers if we buy the trailers. To compare apple to apple in terms of time period, the depreciation have to start AT THE SAME TIME as the lease starts.
i tried to use a "IF" statement to do the calculateion for the depreciation. the formula i created is as the follows:
"=IF(D46>0,C46/10/12,IF(E46>0,C46/10/11,IF(F46>0,C46/10/10,IF(G46>0,C46/10/9,IF(H46>0,C46/10/8,IF(H46>0,C46/10/7,IF(I46>0,C46/10/6,IF(J46>0,C46/10/5,0))))))))"
basically i asked the computer to check that in each month, if there's a value greater than zero, calculates the monthly depreciation times the number of months remains in the year. for example, if there's a lease amount in april, then it will calculates the depreciation as monthly depreciation times 9 [there are 9 months left in the year if we start to count from April (with April included.)]
the above formula that i created work BUT i can only have 9 "IF"s compounded (in a way that an "IF" is imbeded in another "IF"...) is there a limit of compounding like this? i can only go up to September but i need a full year, which means, i need it to be imbeded 12 times!
i know this looks long... but PLEASE HELP!
Tracy