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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
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
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
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
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
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
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,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top