Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I have been using this equation and it has worked fine:
=IF($F2=12,$L2*0.00046,$L2*0.000656)
This equation resides in col. M.
Explanation: If amt in col. F is 12, then multiply amt in col.
L by .00046, otherwise multiply col. L by .000656 (the
amt in col. F is always either a 12 or an 18). The answer
resides in col. M.
Now I have been given a NEW situation to consider:
If the amount in col. L is a zero, then use the amount in
col. W for the calculation. So its not just a true/false
calculation anymore. There is an 'IF" or an "AND" (or both!)
to it.
CAN ANYBODY HELP ME???
It IS still a "true/false" situation, it has now become a more complex "Nested If".
I'm only missing the integer you multiply W by, let's assume for the moment it's 0.000212. The formula now becomes:
=IF(F2=12,L2*0.00046,(=IF(F2=18,L2*0.000656,W2*0.000212)))
There is no space after the 2nd IF, the website broke the calculation there for some reason.
_________________________
The internet is no longer a toy, it's a COMBAT ZONE!
0 
thank you for responding so quickly!! you're a lifesaver!
it's a little more complicated though:
this is the equation you got from my PREVIOUS
instructions:
=IF($F2=12,$L2*0.00046,(IF($F2=18,$L2*0.000656,$W2*
0.000212)))Let me be a little clearer. Sometimes the L column has a
number and sometimes it has a zero. If it has a zero, then
I have to multiply the W column instead. Also, there's a
different multiplier according to whether F has a 12 or an
18 in that column:If F is equal to 12 AND L is not =zero, then multiply
L*.00046. otherwise multiply W*.00046. BUT...
If F is equal to 18 AND L is not =zero, then multiply
L*.000656. otherwise multiply W*.000656.Can you HELP!... again????
0
Might I further assume that your integers (0.00046, 0.000656 & 0.000212 or whatever) are constants?
What if, a year from now, 0.000656 changes to 0.000564? Will you be forced to rewrite the formula?
Place the constants in a secure place like Z1=0.00046, Z2=0.000656 & Z3=0.000212.
The formula now becomes =IF(F2=12,L2*$Z$1,(IF(F2=18,L2*$Z$2,W2*$Z$3)))
This way if any of the integers ever changes you can change them in one place and it will recalculate the entire spreadsheet rather than modifying numerous formulas.
_________________________
The internet is no longer a toy, it's a COMBAT ZONE!
0
OOPS, ignore my last. your response hadn't shown up yet when i posted it. Let me go work on it some more.
_________________________
The internet is no longer a toy, it's a COMBAT ZONE!
0
Great idea about placing the constants in Z1, etc.!! But...
duh... WHERE do you define the Z1, Z2, etc. I just know
about entering the equations in the cells and using the $
to make it whatever row it's copied into.
Anxiously awaiting your replies!
0
=if(L=0;(if(F>12;w*56;w*46));(if(f>12;L*56;L*46))) should do the job, if what u told in ur next message is well understood.
To follow advice about changin value, best way is put ur number in a row, u name this row and next use the name u gave instead of the value itself in ur formula. I explain : u put 46 in a row, give a name like "bill46" and use in ur formula (bill46*w).
0
D.VAND, check your mail. attachment is test.xls
_________________________
The internet is no longer a toy, it's a COMBAT ZONE!
0
Just to be utterly pedantic: An integer is a whole number
And to solve the problem the easiest way: forget nested Ifs. You have two completely independent conditions, so just multiply two if statements.
Excel users very often never learn the fact that an IF statement simply returns a variable - which can, of course, be numeric - and therefore can take numerical treatment:
using row 2=IF(F2=12,0.00046,0.000656)*IF(L2=0,W2,L2)
This solves the problem given your original parameters. You would need to nest Ifs if col F ever contained anything other than a 12 or an 18.
PS
0
Furthermore - it would be worth reading the help files on the following two subjects:
(i) Defining named ranges - so you have a named reference to the cells which contains your constants
(ii) Defining constants - so you actually just use the name of the constant, and the value you want is automatically used.PS
0
"Excel users very often never learn the fact that an IF statement simply returns a variable..."
You are right.
Nice tip!
Thanks for the insight,
Bryan
0
Hey Bryan - glad to give anything at all to someone like you who gives so much to the users of this board.
Cheers
Tom
0
Thank you Tom.
Where have you been? You have left Jen, Grok and myself to carrying the ball on this board :)
Bryan
0
Having to actually do some work during my time at the office. Its a proper nuisance!
Hopefully see you around some more, though
Tom
0
You guys are awesome. I just registered at
computing.net and saw one of your responses to someone who has somewhat of a similar situation to mine.
Hopefully you will be able to help me soon. Here goes,Row 11 is the result of Row 9 + Row 10. If the resulting value in Row 11 is less than 0 or a negative value, then that value needs to be in Row 12. If it is greater than 0, then the value needs to be in Row 11.
Please help me with this formula and if you are ever in Las Vegas, I will voluntarily be your tour guide.
thx
=====
Iris
0
Hi Iris
[Lets say you are in column A]
In cell A11, type:
=If(A9+A10<=0,"",A9+A10)and in cell A12, type:
=If(A11="",A9+A10,"")I have assumed that by "is less than 0 or a negative value" you meant "is less than or equal to zero"
Hope that helps
Tom
0
Iris,
I am surprised that anyone besides myself is still following this thread.
Being new here you need to know that when you ask a new question then you need to create a new thread/post. Doing so will also place your question on the top of the forum board providing the best chances of receiving a speedy response.
Happy computing,
Bryan
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
