Understanding if and countif statements

[Tweetie]

Could someone explain what this formula is saying?

=ROUND(((IF(COUNTIF('X Sheet'!J45:J128,"Apples")>0,IF(COUNTIF('X Sheet'!J45:J128,"Oranges")>0,0.5,1),0))*Management*(VLOOKUP('X Sheet!J31,Groceryprice,4,FALSE)))/100,0)*100

The IF and COUNTIF are throwing me off.

Thanks

[martindwilson]

IF(COUNTIF('X Sheet'!J45:J128,"Apples")>0,IF(COUNTIF('X Sheet'!J45:J128,"Oranges")>0,0.5,1),0))
if the count of apples is greater than 0 then if the count of oranges is greater than 0
return 0.5

if the count of apples is greater than 0 then if the count of oranges is not greater than 0
return 1
if the
if the count of apples is not greater than 0 the next bit about oranges is ignored and it returns 0
the count if is saying
COUNTIF('X Sheet'!J45:J128,"Apples")
count the number of times apple appears in the range J45:J128 on sheet 'X Sheet'

[daddylonglegs]

Your result is rounded to the nearest multiple of 100 using the form

=ROUND(number/100,0)*100

It's simpler to use

=ROUND(number,-2)

to do the same thing, so the formula would become

=ROUND(IF(COUNTIF('X Sheet'!J45:J128,"Apples")>0,IF(COUNTIF('X Sheet'!J45:J128,"Oranges")>0,0.5,1),0)*Management*VLOOKUP('X Sheet!J31,Groceryprice,4,FALSE),-2)