Invalid formula b/c of parentheses?

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
I've been staring at this and can't figure out where the error is. It's saying the formula is invalid, could someone take a look and see if the parentheses are all making sense?

thank you!


=IFERROR("SUMIF(B$3:B3,GRAPH!AF$3,(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2)))/(SUMIF(B$3:B3,GRAPH!AF$3,OPPERA!N$3:N3))", 0)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You need to delete the quotes marked in red

=IFERROR("SUMIF(B$3:B3,GRAPH!AF$3,(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2)))/(SUMIF(B$3:B3,GRAPH!AF$3,OPPERA!N$3:N3))", 0)
 
Upvote 0

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
i did that and it's still invalid. the quotes were just excel entering them after i clicked out of the cell with the invalid formula still in it
 
Upvote 0

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
do i need the second SUM IF? really it should be already qualified as to what I am looking for.

I would need the part after the division sign to have the same qualifications as the first SUM IF
 
Upvote 0

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I've been staring at this and can't figure out where the error is. It's saying the formula is invalid, could someone take a look and see if the parentheses are all making sense?

thank you!


=IFERROR("SUMIF(B$3:B3,GRAPH!AF$3,(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2)))/(SUMIF(B$3:B3,GRAPH!AF$3,OPPERA!N$3:N3))", 0)
Aside from the quotes that need to be removed, what is the sum_range?

The arguments for SUMIF are:

Criteria_range
Criteria
Sum_range

In the first SUMIF function your sum_range argument is not valid.

(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2))
 
Upvote 0

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
Maybe this?

=IFERROR(SUMPRODUCT( (B$3:B3 = GRAPH!AF$3) * (13*OPPERA!R$3:R3 + 3*(OPPERA!O$3:O3 + OPPERA!S$3:S3 - OPPERA!T$3:T3) - 2 * OPPERA!U$3:U3)) / SUMIF(B$3:B3, GRAPH!AF$3, OPPERA!N$3:N3), 0)
 
Upvote 0

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
the sumproduct formula gives me an incorrect value, but i also forgot one piece.

right now I have this

=3.11+ IFERROR((((SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$R7:R7)*13)+((SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$S7:S7) + SUMIF(B$3:B7,GRAPH!AF$3,OPPHBP!$P7:P7)-SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$T7:T7)*3)-(SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$U7:U7)*2)))/(SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$N7:N7))), 0)

and the result is -.53 when the result should be 10.96.

too many parentheses?
 
Upvote 0

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
i had the $ sign in front of the column, instead of the row.

the result is still off though, I'm getting 9.54
 
Upvote 0

Forum statistics

Threads
1,191,025
Messages
5,984,197
Members
439,877
Latest member
kellylet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top