Newbie help with "if"

[t0mteee]

Hi,

I'm attempting to use "IF" (or any other function that might work better) to do the following:

Check if number "1" exist in the field range A1-A5, if it does calculate B1-B5/C1-C5. If another number then do nothing
Then on the next row I will do the same but for number 2, and then number 3.


So, I want it do check a number of field if a specific number exist. If it does, do this calculation. If not do nothing because the calculation will be made on the next or the last row instead (2 or 3). Something like this:

A1:1
A2:2
A3:1
A4:2
A5:3
B1: 20
B2: 25
B3: 30
B4: 40
B5: 20
C1: 100
C2: 200
C3: 150
C4: 200
C5: 100

If 1 = (20+30) / (100+150)
If 2 = (25+40) / (200+200)
If 3 = (30) / (150)

Thanks in advance.

[kev_]

The formula is

=SUMIF(A1:$A$5,1,$B1:$B5)/SUMIF($A$1:A5,1,$C$1:$C$5)

Amend the 1 to 2 or 3 for your other rows


Hiding the value if nothing can be done by wrapping in IFERROR

=IFERROR(SUMIF(A1:$A$5,1,B1:B5)/SUMIF($A$1:A5,1,$C$1:$C$5),"")

which works because formula will divide by 0 if there are not items in the range


EDIT
I amended the 2nd formula when I spotted the divide by zero option

[t0mteee]

Thanks! But it seems like I keep getting a excel error regarding the formula? "There is a problem with this formula" , strange? Can't even add it to a new, empty sheet.

[kev_]

open workbook
look in column E

[davsth]

=IFERROR(SUMIF(A1:$A$5,1,B1:B5)/SUMIF($A$1:A5,1,$C$1:$C$5),"") works but you may wish to add $ to keep the ranges the same size

=IFERROR(SUMIF($A$1:$A$5,1,$B$1:$B$5)/SUMIF($A$1:$A$5,1,$C$1:$C$5),"")

[jeffreybrown]

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...