MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nested if >7


Posted by Bob on April 12, 2001 10:20 AM

Kenny, Looks like this will work!

=IF(A50<=5,(IF(A50=1,C50/SUMIF(A:A,"=1",C:C),IF(A50=2,C50/SUMIF(A:A,"=2",C:C),IF(A50=3,C50/SUMIF(A:A,"=3",C:C),IF(A50=4,C50/SUMIF(A:A,"=4",C:C),IF(A50=5,C50/SUMIF(A:A,"=5",C:C))))))),IF(A50>=6,(IF(A50=6,C50/SUMIF(A:A,"=6",C:C),IF(A50=7,C50/SUMIF(A:A,"=7",C:C),IF(A50=8,C50/SUMIF(A:A,"=8",C:C)))))))


Posted by Bob on April 12, 2001 10:22 AM

You can read this one better

=IF(A50<=5,(IF(A50=1,C50/SUMIF(A:A,"=1",C:C),
IF(A50=2,C50/SUMIF(A:A,"=2",C:C),
IF(A50=3,C50/SUMIF(A:A,"=3",C:C),
IF(A50=4,C50/SUMIF(A:A,"=4",C:C),
IF(A50=5,C50/SUMIF(A:A,"=5",C:C))))))),
IF(A50>=6,(IF(A50=6,C50/SUMIF(A:A,"=6",C:C),
IF(A50=7,C50/SUMIF(A:A,"=7",C:C),
IF(A50=8,C50/SUMIF(A:A,"=8",C:C)))))))

Posted by Aladin Akyurek on April 12, 2001 10:29 AM

Bob

A quick shot. Try the following:

=C50/SUMIF(A:A,A50,C:C)

Does this work?

Aladin

Posted by Mark W. on April 12, 2001 3:16 PM

Good Job! Welcome back


Posted by Bob on April 13, 2001 8:21 AM

Re: Good Job! Welcome back


Well, we are trying to calculate multiple locations that might
have a variable amount of info, then divide by the sum of all the locations.

The 'Mega Formula' works! but I am sure their is a
Array or shorter way to do this, but we had to tackle
the 7 nested if issue by createing a true false via the
A4<=5, then (TRUE), otherwise look for A4>6. Probably not
making a whole lot of sense, but bottom line, it works!


...IF(A4<=5,(IF(A4=1,C4/SUMIF(A:A,"=1",C:C)
...A4 is location, A:A would hold multiple locations up to 10
...c4 = $$$$ divided by the total dollars in that location.

Posted by Aladin Akyurek on April 13, 2001 9:06 AM

Re: Good Job! Welcome back

Bob

SHOULD I CONCLUDE THAT THE SHORTER VERSION THAT I PROVIDED DOESN'T WORK? IF NOT, WHY?

but we had to tackle

MY SOLE INTENTION WAS NOTHING BUT TO PROVIDE YOU A SHORTER VERSION THAT SHOULD ALSO WORK.

Aladin