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
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'
Last edited by martindwilson; 03-24-2009 at 04:24 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
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)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks