Are the parentheses the problem?

M

Mark74w1

The formula is to show t's Share of the profit.

=SUM(U14)-((SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plu
Invoice'!T8:T1320))+((SUMIF('Cost Plus Invoice'!O8:O1510,"m",'Cost Plu
Invoice'!T8:T1320))+((SUMIF('Cost Plus Invoice'!O8:O1510,"r",'Cost Plu
Invoice'!T8:T1320))+((SUMIF('Cost Entry'!O7:O1320,"t",'Cos
Entry'!T7:T1320))+((SUMIF('Cost Entry'!O7:O1320,"m",'Cos
Entry'!T7:T1320))+((SUMIF('Cost Entry'!O7:O1320,"r",'Cos
Entry'!T7:T1320)))+(Phases!L29)*0.55+('Profit & Loss'!U15))))))
Payments by customer: $2587.11
Total cost of the project: - $1178.68
__________
Total Profit: $1408.43

"t"'s Share (55%) of the profit: x.55= $ 774.77
Plus the amounts paid by "t" $ 928.43
_________
"t"'s total share: $1703.20

=SUM(U14 is $2587.11
=SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T132
is $ 422.11
=SUMIF('Cost Plus Invoice'!O8:O1510,"m,'Cost Plus Invoice'!T8:T1320
is $ 150.00
=SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T132
is $ 100.00
=SUMIF('Cost Entry'!O8:O1510,"t",'Cost Entry'!T8:T1320 is
377.36 =SUMIF('Cost Entry'!O8:O1510,"m",'Cost Entry'!T8:T1320 is
000.00
=SUMIF('Cost Entry'!O8:O1510,"r",'Cost Entry'!T8:T1320 is $ 000.00
+(Phases!L29 is $128.96 [payroll]
+('Profit & Loss'!U15 is $928.43 [paid by "t"

Why is this formula not working even when excel corrects it
 
R

Ron Rosenfeld

Why is this formula not working even when excel corrects it?

What do you mean by "not working"?
Returns an error? If so, what is the error?
Causes Excel to crash?
Returns an incorrect result? If so, what is the original data and what is the returned result? (This might be more easily transferred by posting a workbook on some accessible site (e.g. Skydrive) and posting a link here.
 
M

Mark74w1

'Ron Rosenfeld[_2_ said:
;1612717']On Sun, 14 Jul 2013 00:55:00 +0100, Mark74w
Why is this formula not working even when excel corrects it?-

What do you mean by "not working"?
Returns an error? If so, what is the error?
Causes Excel to crash?
Returns an incorrect result? If so, what is the original data an
what is the returned result? (This might be more easily transferred b
posting a workbook on some accessible site (e.g. Skydrive) and posting
link here.

Thank you for responding Ron.
The total done on a calculator is the correct results as I showed i
the math example, but the results shown in the target cell is
different total.
If I move the parentheses to change the way it groups the answer i
still wrong.
I'm new to Skydrive and would be glad to get the workbook to you if
new how..
Thanks, Mar
 
R

Ron Rosenfeld

'Ron Rosenfeld[_2_ said:
;1612717']On Sun, 14 Jul 2013 00:55:00 +0100, Mark74w1
Why is this formula not working even when excel corrects it?-

What do you mean by "not working"?
Returns an error? If so, what is the error?
Causes Excel to crash?
Returns an incorrect result? If so, what is the original data and
what is the returned result? (This might be more easily transferred by
posting a workbook on some accessible site (e.g. Skydrive) and posting a
link here.

Thank you for responding Ron.
The total done on a calculator is the correct results as I showed in
the math example, but the results shown in the target cell is a
different total.
If I move the parentheses to change the way it groups the answer is
still wrong.
I'm new to Skydrive and would be glad to get the workbook to you if I
new how..
Thanks, Mark


For SkyDrive, see instructions here: http://windows.microsoft.com/en-us/...23e-42c2-9c6e-79fa17a5e3df#skydrive-help=tab1
You do need a Microsoft or Hotmail account to use it.


There are other sharing sites, also.
 
J

joeu2004

Mark74w1 said:
Subject: Are the parentheses the problem?

Yes, that is the problem, for the most part. Try the following:

=(U14
-(SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T1320)
+SUMIF('Cost Plus Invoice'!O8:O1510,"m",'Cost Plus Invoice'!T8:T1320)
+SUMIF('Cost Plus Invoice'!O8:O1510,"r",'Cost Plus Invoice'!T8:T1320)
+SUMIF('Cost Entry'!O7:O1320,"t",'Cost Entry'!T7:T1320)
+SUMIF('Cost Entry'!O7:O1320,"m",'Cost Entry'!T7:T1320)
+SUMIF('Cost Entry'!O7:O1320,"r",'Cost Entry'!T7:T1320)
+Phases!L29))*0.55+'Profit & Loss'!U15

Even simpler:

=(U14
-SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T1320)
-SUMIF('Cost Plus Invoice'!O8:O1510,"m",'Cost Plus Invoice'!T8:T1320)
-SUMIF('Cost Plus Invoice'!O8:O1510,"r",'Cost Plus Invoice'!T8:T1320)
-SUMIF('Cost Entry'!O7:O1320,"t",'Cost Entry'!T7:T1320)
-SUMIF('Cost Entry'!O7:O1320,"m",'Cost Entry'!T7:T1320)
-SUMIF('Cost Entry'!O7:O1320,"r",'Cost Entry'!T7:T1320)
-Phases!L29)*0.55+'Profit & Loss'!U15

However, multiplying dollar-and-cents by 0.55 is risky: often, the result
is not exact to the penny. For example, 1.23*0.55 is 0.6765. If you
display with only 2 decimal places, it will __look__ like 0.68. But it is
still __really__ 0.6765. That might adversely affect dependent calculation
elsewhere.

So the best formula is:

=ROUND((U14
-SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T1320)
-SUMIF('Cost Plus Invoice'!O8:O1510,"m",'Cost Plus Invoice'!T8:T1320)
-SUMIF('Cost Plus Invoice'!O8:O1510,"r",'Cost Plus Invoice'!T8:T1320)
-SUMIF('Cost Entry'!O7:O1320,"t",'Cost Entry'!T7:T1320)
-SUMIF('Cost Entry'!O7:O1320,"m",'Cost Entry'!T7:T1320)
-SUMIF('Cost Entry'!O7:O1320,"r",'Cost Entry'!T7:T1320)
-Phases!L29)*0.55+'Profit & Loss'!U15, 2)

Caveat: That assumes that each of those cells are constants or their
formulas have been rounded to 2 decimal places.


----- original message -----
Mark74w1 said:
The formula is to show t's Share of the profit.

=SUM(U14)-((SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus
Invoice'!T8:T1320))+((SUMIF('Cost Plus Invoice'!O8:O1510,"m",'Cost Plus
Invoice'!T8:T1320))+((SUMIF('Cost Plus Invoice'!O8:O1510,"r",'Cost Plus
Invoice'!T8:T1320))+((SUMIF('Cost Entry'!O7:O1320,"t",'Cost
Entry'!T7:T1320))+((SUMIF('Cost Entry'!O7:O1320,"m",'Cost
Entry'!T7:T1320))+((SUMIF('Cost Entry'!O7:O1320,"r",'Cost
Entry'!T7:T1320)))+(Phases!L29)*0.55+('Profit & Loss'!U15))))))
Payments by customer: $2587.11
Total cost of the project: - $1178.68
__________
Total Profit: $1408.43

"t"'s Share (55%) of the profit: x.55= $ 774.77
Plus the amounts paid by "t" $ 928.43
_________
"t"'s total share: $1703.20

=SUM(U14 is $2587.11
=SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T1320
is $ 422.11
=SUMIF('Cost Plus Invoice'!O8:O1510,"m,'Cost Plus Invoice'!T8:T1320
is $ 150.00
=SUMIF('Cost Plus Invoice'!O8:O1510,"t",'Cost Plus Invoice'!T8:T1320
is $ 100.00
=SUMIF('Cost Entry'!O8:O1510,"t",'Cost Entry'!T8:T1320 is $
377.36 =SUMIF('Cost Entry'!O8:O1510,"m",'Cost Entry'!T8:T1320 is $
000.00
=SUMIF('Cost Entry'!O8:O1510,"r",'Cost Entry'!T8:T1320 is $ 000.00
+(Phases!L29 is $128.96 [payroll]
+('Profit & Loss'!U15 is $928.43 [paid by "t"

Why is this formula not working even when excel corrects it?
 
J

joeu2004

Mark74w1 said:
I'm new to Skydrive and would be glad to get the workbook
to you if I new how.

skydrive.live.com is the worst file-sharing website, IMHO. Its only benefit
for users of other Microsoft websites (e.g. hotmail.com) is: they can use
the same login for Skydrive.

Some other file-sharing websites are:

Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

I use box.net. It is easy to set up a free login. After you upload the
file (straight-forward), click on Share, copy the URL ("link to this file"),
and paste it into a posting here.

Caveat: box.net has made a change recently that I do not like: we can no
longer disable preview when someone tries to download the file. Preview
does not work in some cases because it does not support some Excel features.
Just ignore any preview errors and download the file.
 
R

Ron Rosenfeld

skydrive.live.com is the worst file-sharing website, IMHO. Its only benefit
for users of other Microsoft websites (e.g. hotmail.com) is: they can use
the same login for Skydrive.

Some other file-sharing websites are:

Since I have a MS account, for accessing their various groups, that's not a problem (I never use hotmail).
Two other uses that are good for me is that the contents can be synchronized across various computers/devices;
and it appears as a folder in Windows Explorer, enabling easy transfer of files to/from.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

if cell is blank 8
Negative value to positive 13

Top