Thread for posting confusing functions

[ADoubleDot]

Originally Posted by JMO

How about this one?
=IF(ROW()-ROW($H$2)+COLUMN()-COLUMN($H$2)>3,"",SUM(OFFSET ($B$2,ROW()-ROW($H$2),ROW()-ROW($H$2),COLUMN()-COLUMN($H$2)+1,1)))

Spoiler:

From the article next after yours in the newsletter.

what???

[vividox]

Wow, that's just dumb.

[limabeanactuary]

I wonder if I will get much in the way of people writing me ... "What's so bad with formulas like that? I use those sorts of things all the time!"

Probably not, but you never know.

[JMO]

Originally Posted by limabeanactuary

I wonder if I will get much in the way of people writing me ... "What's so bad with formulas like that? I use those sorts of things all the time!"

Probably not, but you never know.

Many more people will think it than actually take time to write to you.

[ADoubleDot]

More than likely they will think that they have complex probelms to solve and if something works they are going to use it without regard to MPC's OCD

[limabeanactuary]

Yes, I try to stick to working only on simple stuff.

So far, that's not worked out for me.

[Gedankenexperiment]

Inherited an obscenely large workbook with

=IF(F2>0,$F$1,IF(G2>0,$G$1,IF(H2>0, $H$1, IF(I2>0, $I$1, IF(J2>0, $J$1, IF(K2>0, $K$1, IF(L2>0, $L$1, IF(M2>0, $M$1, IF(N2>0, $N$1, IF(O2>0, $O$1, IF(P2>0, $P$1, IF(Q2>0, $Q$1, 0))))))))))))

and

=IF(MOD(AG44, 12)=0, VLOOKUP(AG44/12+33, $F$18:$AD$129, 25, TRUE)/12, AI43)

To keep it from killing my computer, I deleted half of the sheets and added formulas like:

=SUMPRODUCT('Claims'!E$2:J$27831*'Claims'!$B$2:$B$ 27831,--(COLUMN('Claims'!E$2:J$27831)-4-C1='Claims'!$A$2:$A$27831))/SUM('Claims'!$B$2:$B$27831)

[JMO]

Why would a person code in a formula that looks like this?

=(PRODUCT(B20,1+$E$7/4)+SUM(C20))/1000

I understand the Product, although with that few arguments why not just multiply?
But the idea of using the sum function to add one more cell just seems incomprehensible.

[limabeanactuary]

I think I see what I'm going to start the next installment of my series with... start with something just simply bad, and then go to the horror, the horror.

[Gedankenexperiment]

Originally Posted by JMO

Why would a person code in a formula that looks like this?

=(PRODUCT(B20,1+$E$7/4)+SUM(C20))/1000

I understand the Product, although with that few arguments why not just multiply?
But the idea of using the sum function to add one more cell just seems incomprehensible.

Okay so first of all, what you want to do, is add up all the numbers that are equal to 16.