Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
im writing a VBA programme where x = 12 in cell A3 and cell B3 = 120
and in cell C3 =IF<, IF(B3<=0," Integer in B3 must be positive",(A3^B3)/B3) this gives the value 4.9922E+181 when i change cell B3 to a negative value it shows " integer in b3 must be positive is this right??? where i want cell C3 to show the value of x raised to the power n divided by n factorial? and want to show that a negative value of n canot be used, where n is cell b3 aslo do i need to qrite a vba for this too work??? also is there anyway of checking for errors in this?? thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
I don't understand the initial =IF<, being there at all. I'd have simply
entered =IF(B3<=0,"B3 must be integer greater than zero",(A3^B3)/FACT(B3)) FACT (B3) will give you the Factorial of the value in B3. As for validating the formula, there's no way for the computer to know if the logic you've used or functions you've used are the proper ones or not. What you can do is take values and work it out by hand and then using those same values, see if the formula gives you the same result. If it doesn't, you first check your pencil and paper results and if those are certain in your mind, then you figure out what's wrong with your formula. You can even break your formula into its parts and validate that each piece returns the proper value. In your case, lets take a couple of small values to work with: A3 = 2 B3 = 5 2^3 = 8 5 Factorial = 5*4*3*2*1 = 120 and 8/120 = .2666666..... although Excel will probably display .266667 You could test the interim results by putting =A3^B3 into a cell and observing that it returns 8. and putting = FACT(B3) into another and observing that it returns 120. "biker man" wrote: im writing a VBA programme where x = 12 in cell A3 and cell B3 = 120 and in cell C3 =IF<, IF(B3<=0," Integer in B3 must be positive",(A3^B3)/B3) this gives the value 4.9922E+181 when i change cell B3 to a negative value it shows " integer in b3 must be positive is this right??? where i want cell C3 to show the value of x raised to the power n divided by n factorial? and want to show that a negative value of n canot be used, where n is cell b3 aslo do i need to qrite a vba for this too work??? also is there anyway of checking for errors in this?? thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
actually, my pen and pencil slipped! Should have written
2^5 = 32 (not 2^3 =8, since B3 = 5: I had 3 on my mind [A3, B3, C3]). and finally it is 32/120 that = .2666666... not 8/120. "JLatham" wrote: I don't understand the initial =IF<, being there at all. I'd have simply entered =IF(B3<=0,"B3 must be integer greater than zero",(A3^B3)/FACT(B3)) FACT (B3) will give you the Factorial of the value in B3. As for validating the formula, there's no way for the computer to know if the logic you've used or functions you've used are the proper ones or not. What you can do is take values and work it out by hand and then using those same values, see if the formula gives you the same result. If it doesn't, you first check your pencil and paper results and if those are certain in your mind, then you figure out what's wrong with your formula. You can even break your formula into its parts and validate that each piece returns the proper value. In your case, lets take a couple of small values to work with: A3 = 2 B3 = 5 2^3 = 8 5 Factorial = 5*4*3*2*1 = 120 and 8/120 = .2666666..... although Excel will probably display .266667 You could test the interim results by putting =A3^B3 into a cell and observing that it returns 8. and putting = FACT(B3) into another and observing that it returns 120. "biker man" wrote: im writing a VBA programme where x = 12 in cell A3 and cell B3 = 120 and in cell C3 =IF<, IF(B3<=0," Integer in B3 must be positive",(A3^B3)/B3) this gives the value 4.9922E+181 when i change cell B3 to a negative value it shows " integer in b3 must be positive is this right??? where i want cell C3 to show the value of x raised to the power n divided by n factorial? and want to show that a negative value of n canot be used, where n is cell b3 aslo do i need to qrite a vba for this too work??? also is there anyway of checking for errors in this?? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|