Thread for posting confusing functions

[campbell]

Originally Posted by vividox

Wow, you've got to be kidding me. Same worksheet:

=IF(L3=0,IF(K3=0,0,IF(K3=100,19,IF(K3=300,20,IF(K3 =500,21,IF(K3=800,22,23))))),IF(K3=25,IF(L3=25,1,2 ),IF(K3=50,IF(L3=25,3,IF(L3=50,4,IF(L3=100,5,6))), IF(K3=100,IF(L3=25,7,IF(L3=50,8,IF(L3=100,9,10))), IF(K3=250,IF(L3=25,11,IF(L3=50,12,IF(L3=100,13,IF( L3=250,14,15)))),IF(K3=500,IF(L3=100,16,IF(L3=250, 17,18)),0))))))

Thanks for your contribution!

Let's see, I think I already submitted the 3rd edition. I'll have to check.

[vividox]

Originally Posted by campbell

Thanks for your contribution!

Let's see, I think I already submitted the 3rd edition. I'll have to check.

How many times have I appeared in your literature now?

[campbell]

Originally Posted by vividox

How many times have I appeared in your literature now?

At least twice, I'm thinking.

[whisper]

Originally Posted by JMO

Of course, before writing any nested IF, people should be required to use a structure such as a truth table so that they can analyze and perhaps find a simpler way to express the condition. JMO, you know.

Another important consideration is that if statements can reduce the number of calculations. So, if you have if(Condition, formula 1, formula 2), formula 1 will only trigger if you have the true condition.

[whisper]

Originally Posted by limabeanactuary

GETPIVOTDATA?!?!?!?!

Is there a more fundamentally wrong idea that MS has created worse than getpivotdata? Especially since MS "helps" you by automatically creating this formula for you?

My #1 spread sheet rule:
Never, ever let Excel think for itself. Even when I'm doing a range look-up table, I explicitly create boundaries so I know I'm controlling Excel decision making.

[ADoubleDot]

I think people crapping on GETPIVOTDATA just don't know how to use it.

[campbell]

Please tell me how to use GETPIVOTDATA when you have a pivot table that gets changed.

(If the pivot table never changed, you could always use SUMIFs, etc.)

[ADoubleDot]

Changed like how? Moving stuff around, removing/adding variables. Can you think of a solution where this would be easy to do? I'm familiar enough with the tables and function to do it. But that's not a fair comparison to a lookup table. Where you have to remake the table and the function and both are labor intensive.

Further, I think look up tables contain maintainability issues (copy/paste issues; number of rows changing) and SUMIFs is less clear (unless you are naming your ranges). In the sumifs, i have no idea whats going on without searching though the workbook. In the GPD, I know exactly what's going on, Looking up the dollars for the contract in e1 and the planName in d1.

SUMIFS($A$4:$A$8,$H$4:$H$9,D$1,$I$4:$I$9,E$1)
GETPIVOTDATA("Dollars",$A$3,"PlanName",D$1,"Contra ct",E$1)

[whisper]

Originally Posted by campbell

Please tell me how to use GETPIVOTDATA when you have a pivot table that gets changed.

You'd have to set the Getpivotdata formula elements to ranges that you can adjust as the pivot table adjusts.

It's possible - but the added complexity isn't worth the extra effort.

[vividox]

Originally Posted by ADoubleDot

Changed like how? Moving stuff around, removing/adding variables. Can you think of a solution where this would be easy to do? I'm familiar enough with the tables and function to do it. But that's not a fair comparison to a lookup table. Where you have to remake the table and the function and both are labor intensive.

Further, I think look up tables contain maintainability issues (copy/paste issues; number of rows changing) and SUMIFs is less clear (unless you are naming your ranges). In the sumifs, i have no idea whats going on without searching though the workbook. In the GPD, I know exactly what's going on, Looking up the dollars for the contract in e1 and the planName in d1.

SUMIFS($A$4:$A$8,$H$4:$H$9,D$1,$I$4:$I$9,E$1)
GETPIVOTDATA("Dollars",$A$3,"PlanName",D$1,"Contra ct",E$1)

When I use SUMIFs on tables, I just make sure that no other data is in the column and write:

SUMIFS(A:A,H:H,D$1,I:I,E$1)

Then you can add as many rows of data as you want and don't have to worry about updating your formulas. Even if you insert columns or copy/paste, the formulas update themselves. The only thing you have to worry about is cut/paste.