If condition meet

[maabbas]

=IF(A131=Yield!A1:A1020,(Sun!B131+Sun!P131)/Sun!K131,Sun!B131/Sun!K131)

Any help, I am not getting the right calculation

 

[Aladin Akyurek]

Sorry I posted the wrong formula, please check the below formula, I tried using the CRTL SHIFT ENTER, but formula only calcuting right the first cell of range

=IF(Yield!A757:A1020=Sun!A131,Sun!B131/Sun!K131,IF(Yield!A1:A756=Sun!A131,(Sun!B131+Sun!P131)/Sun!K131,0))

A mapping like IF(Yield!A757:A1020=Sun!A131,Sun!B131/Sun!K131,... is wrong (unless there is some strange justification for it. It says:

Whenever a cell in Yield!A757:A1020 is equal to Sun!A131, do Sun!B131/Sun!K131, which is a division involving two cells, meaning that you repeatedly perform the same division. It looks like you want but not certain...

=IF(ISNUMBER(MATCH(Sun!A131,Yield!A757:A1020,0)),
     Sun!B131/Sun!K131,
     IF(ISNUMBER(MATCH(Sun!A131,Yield!A1:A756,0)),
         Sun!B131+Sun!P131)/Sun!K131,
         0))

See also Marcelo Branco's reply.

 

[Marcelo Branco]

its working but only for range A1 to A756, I have more product from range A757 to A1020 where I want formula to calculate Pound produce/Raw(B131/K131).

I think it works for both ranges since accordingly with your formula in #3 if the product is located in A757:A1020 (not in A1:A756) the MATCH part returns #N/A and ISNUMBER(#N/A) returns FALSE

So the formula evaluates as

(B131 + ISNUMBER(#N/A)*P131)/K131 = (B131 + (FALSE)*P131)/K131 = (B131 + 0)/K131 = B131/K131

Isn't this what you need?

M.

 

[maabbas]

A mapping like IF(Yield!A757:A1020=Sun!A131,Sun!B131/Sun!K131,... is wrong (unless there is some strange justification for it. It says:

Whenever a cell in Yield!A757:A1020 is equal to Sun!A131, do Sun!B131/Sun!K131, which is a division involving two cells, meaning that you repeatedly perform the same division. It looks like you want but not certain...

=IF(ISNUMBER(MATCH(Sun!A131,Yield!A757:A1020,0)),
     Sun!B131/Sun!K131,
     IF(ISNUMBER(MATCH(Sun!A131,Yield!A1:A756,0)),
         Sun!B131+Sun!P131)/Sun!K131,
         0))

See also Marcelo Branco's reply.

Sorry for delay, took some days off, so far the above formula working great, if any issue I will contact you all. Thanks for everyone.