TechArena Community A small confusion with the formula calculation in Excel 2007

#1
28-04-2012
 Member Join Date: Mar 2012 Posts: 41
A small confusion with the formula calculation in Excel 2007

I have developed a small formula in Excel 2007, I am trying to add another and the % increaser works perfect. But in case of decreasing it should be increasing the cost but is rather decreasing it.

IF(\$D862<=50,(\$D862*J\$2),IF(AND(\$D862>50,\$D862<=15 0),(\$D862*K\$2),IF(AND(\$D862>=151,\$D862<=700),(\$D86 2*L\$2),IF(AND(\$D862>=701,\$D862<=10000),(\$D862*M\$2) ,IF(AND(\$D862>=1001,\$D862<=2000),(\$D862*N\$2),IF(AN D(\$D862>=2001,\$D862<=3000)*(\$D862*O\$2),IF(AND(\$D86 2>=3001),\$D862*P\$2)))))))

Can anyone come up with a reliable solution. Desperately awaiting one since I am working on deadlines.
#2
28-04-2012
 Member Join Date: Jul 2011 Posts: 618
Re: A small confusion with the formula calculation in Excel 2007

I would recommend the use of the following chnges in the formula, please checkup and try out if this works your purpose:

=\$D862*lookup(\$d862,{0,50,150,700,1000,2000,3000}, {J\$2\$2})

please make sure you post back it works because this might benefit other users too as I myself have not used it, giving you a fresh suggestion, to try.
#3
28-04-2012
 Member Join Date: Mar 2012 Posts: 41
Re: A small confusion with the formula calculation in Excel 2007

Hi and thanks a lot for the suggestion it actually worked. I would recommend the use of lookup function to everyone. However, I am not very well versed with the lookup function but considering the fact that it is so useful and crucial at situations, I would love to learn more about it.

thanks again, I am obliged!

 Tags: