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
