HELP!! I need an excel / math expert.

[Fishbone†]

Something is wrong with my formula..... I'm sure in the formatting of it, but I can't figure it out..... I'm getting an inflated answer, and maybe it's just cuz my eyes are bugging out trying to reconfigure it, but I can't get it to work..... I believe the problem in the placement of the parantheses, so I think you can "ignore" the links...... ARGHH!!! Someone PLEASE help me!!! Oh, by the way, I'm subtracting my cost of goods from my sales and dividing it by my sales to get my gross profit percent..... OH MY GOODNESS, I don't even know what I'm doing anymore!!!

=SUM(((SUM(IF(Worksheet!$E$4:$E$9911=$A30,IF(Worksheet!$A$4:$A$9911=2003,IF(Worksheet!$B$4:$B$9911=$C$5,IF(Worksheet!$C$4:$C$9911="actuals",Worksheet!$K$4:$K$9911,0),0),0),0)))-(SUM(IF(Worksheet!$E$4:$E$9911=$A30,IF(Worksheet!$A$4:$A$9911=2003,IF(Worksheet!$B$4:$B$9911=$C$5,IF(Worksheet!$C$4:$C$9911="actuals",Worksheet!$P$4:$P$9911,0),0),0),0))))/(SUM(IF(Worksheet!$E$4:$E$9911=$A30,IF(Worksheet!$A$4:$A$9911=2003,IF(Worksheet!$B$4:$B$9911=$C$5,IF(Worksheet!$C$4:$C$9911="actuals",Worksheet!$K$4:$K$9911,0),0),0),0))))

P.S. I know it's an array formula, and have set it to be, so that's not the problem.

 

[Loubon]

The answer is......3.


Actually, it is too hard to fix it this way especially since you can't cut and paste back to Excel from here. If you want to e-mail it I can take a look at it that way. If not start from the middle and work outward to check your (). If that doesn't work you may be nesting too many conditions in one formula. Try breaking it up.

You did at least make The DIS Class Yearbook as a consolation prize....

 

[iNTeNSeBLue98]

Much too complicated for me. I hope you get it fixed.

 

[Serena]

Just a bump since I'm totally clueless. good luck

 

[Fishbone†]

since you can't cut and paste back to Excel from here.

Oh, you're right Loubon.... I wonder why it does that.... I didn't post it here with breaks..... oh well. I would email it, but I don't know if it will help considering you don't have my external link source..... *sigh*..... Tamie just suggested rewriting it from scratch.... I think I'll try that. Sometimes when you work on something for so long, it's hard to "see" the problem. Thank you for trying / offering. Oh, and thanks for the yearbook entry..... cute!!!

Ahh.... back to the drawing board.......

 

[jfulcer]

I agree with loubon, too complicated without the sheet to go with it.

If you want to email it, my wife (who trains people how to use Excel) and I can peek at it.

 

[#1 Disney Fan]

Okie... here goes!

Now keep in mind this is really hard since your references all apply to your specific workbook and so I can't really "try it out" to see what happens and just play around with the formula myself. I agree with everyone else... it's really too complicated without seeing the "sheet" that goes behind it all. But... keeping the BEMDAS rules in mind, and counting brackets very carefully... I might have a couple of suggestions for you.

First of all... my suggestion would be to not attempt to calculate so much in one cell. (As others here have recommended). By breaking it up it's a lot easier to see where your numbers are coming from and where your mistakes are. If I understand correctly you want: ANSWER = (SALES - GOODS) / SALES. ... Not ANSWER = Sales - (Goods/Sales). I think right now you *might* be calculating the second one due to the placement of brackets, which of course would give you an inflated answer. So... I would make a cell to calculate sales, a cell to caculate goods, and then a cell to do the "math." For instance, calculate sales in A1, goods in B1, and the answer in C1. Therefore C1 would be =(A1-B1)/A1 . My other recommendation if you want to keep it as one big cell, to put in an extra set of brackets around the first half of the formula, one opening bracket at the start and a closing bracket before the division sign, to ensure that it is calculating the first half of the formula before doing the division.

 

[FloridaCat]

Send me a PM outlining clearly what you are attempting to do and your E-mail address. It appears you have to many nests and I can't understand the need for an array unless there are a lot of columns involved.

I have wriiten close to a 100 spreadsheets, so I most likly have someting close..