Excel formula not working...Why??
Excel formula not working...Why??
Author
Discussion

tvradict

Original Poster:

3,829 posts

305 months

Sunday 2nd May 2010
quotequote all
Bit of background. I have information in 2 cells I13 and Q13. I need a formula in a third cell, J13, to look at Q13, and then multiply the number in I13 by the relevant amount.

I've created an appropriate Nested IF Function, but for some reason its not working.

IF(Q13=1,J13=TIME(0,I13*60,0),IF(Q13=2,J13=TIME(0,I13*30,0),IF(Q13=3,J13=TIME(0,I13*20,0),IF(Q13=4,J13=TIME(0,I13*15,0),IF(Q13=5,J13=TIME(0,I13*13,0),IF(Q13=6,J13=TIME(0,I13*10,0)))))))

The information in Q13 is in a drop down box list, 1 - 6. The figure displayed in I13 needs to be in Time.

At the moment i Have 4 in Q13 and 4 in J13, so I13 should display 01:00. But its displays 00:00.

Please Help frown

Cheers
Stuart.

twister

1,594 posts

267 months

Sunday 2nd May 2010
quotequote all
Try putting the following into J13

=IF(Q13=1,TIME(0,I13*60,0),IF(Q13=2,TIME(0,I13*30,0),IF(Q13=3,TIME(0,I13*20,0),IF(Q13=4,TIME(0,I13*15,0),IF(Q13=5,TIME(0,I13*13,0),IF(Q13=6,TIME(0,I13*10,0)))))))

mft

1,752 posts

253 months

Monday 3rd May 2010
quotequote all
Two thoughts:

Firstly, however you do it, I suspect you might want 12, not 13, where I've emboldened it:

=IF(Q13=1,TIME(0,I13*60,0),IF(Q13=2,TIME(0,I13*30,0),IF(Q13=3,TIME(0,I13*20,0),IF(Q13=4,TIME(0,I13*15,0),IF(Q13=5,TIME(0,I13*13,0),IF(Q13=6,TIME(0,I13*10,0)))))))


Secondly, this formula is a lot simpler:

=TIME(0,I13*(60/Q13),0)

tvradict

Original Poster:

3,829 posts

305 months

Monday 3rd May 2010
quotequote all
twister said:
Try putting the following into J13

=IF(Q13=1,TIME(0,I13*60,0),IF(Q13=2,TIME(0,I13*30,0),IF(Q13=3,TIME(0,I13*20,0),IF(Q13=4,TIME(0,I13*15,0),IF(Q13=5,TIME(0,I13*13,0),IF(Q13=6,TIME(0,I13*10,0)))))))
Thanks. That worked. thumbup

tvradict

Original Poster:

3,829 posts

305 months

Monday 3rd May 2010
quotequote all
mft said:
Two thoughts:

Firstly, however you do it, I suspect you might want 12, not 13, where I've emboldened it:

=IF(Q13=1,TIME(0,I13*60,0),IF(Q13=2,TIME(0,I13*30,0),IF(Q13=3,TIME(0,I13*20,0),IF(Q13=4,TIME(0,I13*15,0),IF(Q13=5,TIME(0,I13*13,0),IF(Q13=6,TIME(0,I13*10,0)))))))


Secondly, this formula is a lot simpler:

=TIME(0,I13*(60/Q13),0)
Nice spot smile, Thanks.

That formula is much easier. Cheers thumbup