Apply IF Statement on each cell in specified range

[Mr_Patrick]

Hi everyone,

I am trying to define a formula which takes the product of a specified range, where, if the value of any cell (taken individually!) happens to be between (-1,1), then the function will first take the inverse of those values before multiplying every value together. The function is also told to ignore zero values.

For example, if I have
[TABLE="width: 200"]
<tbody>[TR]
[TD]2
[/TD]
[TD]0.4
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]-0.9
[/TD]
[/TR]
</tbody>[/TABLE]

then I wish my function to calculate : 2*(1/0.4)*1*(1/-0.9)
I have tried something like :

{=PRODUCT(IF(A1:E1<>0;IF(AND(-1<=A1:E1;A1:E1<=1);1/A1:E1;A1:E1)))<B5:i5;b5:i5<1);1 B5:I5;B5:I5)))
}
<B5:i5;b5:i5<1);1 A1:E1;A1:E1)))

<B5:i5;b5:i5<1);1 B5:I5;B5:I5)))}
The above function correctly ignores zero valued cells, but I noticed that the argument of PRODUCT is either be the whole A1:E1 or each of its values inverted.
I would like the function to check each cell individually and apply the inversion when the condition is met, before patching up the values and feeding them to the Product function
<B5:i5;b5:i5<1);1 B5:I5;B5:I5)))
Can someone help me out?
<B5:i5;b5:i5<1);1 B5:I5;B5:I5)))

Thank you in advance.</B5:i5;b5:i5<1);1></B5:i5;b5:i5<1);1></B5:i5;b5:i5<1);1></B5:i5;b5:i5<1);1></B5:i5;b5:i5<1);1>

 

[FormR]

Hi, welcome to the forum!

You can't use the AND() function like that in array formula, here is one possible alternative.

{=PRODUCT(IF(A1:E1<>0,IF((A1:E1<1)*(A1:E1>-1),1/A1:E1,A1:E1)))}

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=PRODUCT(IF((ABS(A1:D1)<=1)*(A1:D1<>0),1/A1:D1,A1:D1))

 

[Mr_Patrick]

Amazing thank you a lot! Both suggestions are what I was looking for!

I see that I still have a lot to learn in Excel.