I have a spreadsheet that the formula in Column V will return the same number no matter what I multiple it by.
Column V has : K2*U2
Column K has formula:=AVERAGE((OFFSET(INDEX(_HST_WK,MATCH($A2,HST_WK!$A:$A,0),1),0,Yr_Begin,1,Yr_End))) and is formatted as a "Number"
Column U has formula: =IFERROR((T2*S2)/K2,0) and is formatted as a number. Also T2 and S2 are numbers.
No matter what Column V is multiplying by it results as the same number 130,000. It could be 12 X 1 and the result is 130,000.
It would help if you could post a sample sheet - Thx
Do you see 'Calculate' in the status bar at the bottom left of the screen?
Remember what the dormouse said
Feed your head
No I do not see "calculate", just "ready"
You have a * and / cancelling each other out.
V has U2 multiplied by K2
But then the formuila in U2 is Devided by K2
Those cancel each other out
example
X*10/X = 10, regardless of the value of X
So your formula in column V essentially boils down to =T2*S2
Last edited by Jonmo1; 10-01-2014 at 08:37 AM.
Sample.xlsx
Here is a sample Worksheet
That is true, but it looks like they are different numbers in which case they would not be canceling each other outOriginally Posted by Jonmo1
=IFERROR((T2*S2)/K2,0)
T * S / K
or
5*10/25 = 2
Unless I missed something?
Last edited by Speshul; 10-01-2014 at 08:42 AM.
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.
Jommo1. I see your point. Now I just need to fix itThank you.
Here's what's happening..
V2 has
=K2*U2
U2 has
=IFERROR((T2*S2)/K2,0)
It's irrelevant what the result of T2*S2 is, so long as it's a numeric result...
So let's just say T2 = 5 and S2 = 4, so T2*S2=20
U2 becomes
=IFERROR(20/K2,0)
It's also irreleveant what the result of K2 is, as long is it's a numeric result, say it's 4
So U2 becomes
=IFERROR((20/4,0)
That is Not an error so it's
=20/4
And V2 (=K2*U2) becomes
=4*U2
Then it becomes
=4*20/4
The 4's cancel each other out
Glad to help.Jommo1. I see your point. Now I just need to fix it
Instead of trying to 'fix' it..
It might be better to just start over and describe what you want the formula(s) to do
I found what happened. I agree with you. I am dividing by the same number then multiplying it back. Which creates a sort of redundancy. Column V is basically looking for the same value in Column S. Thanks for the help. I think I just needed new eyes on it.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks