Help with IF stament (getting result FALSE in some cases)

[Mark D]

Good morning everyone.

I have the following calculation that is causing me a problem

=IF(G109<70%,SUM(G90-G96)*AVERAGE('Base
Data'!$I$31:$I$35,IF(G109>70.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109>100%,SUM(G90-G96)*AVERAGE('Base
Data'!$G$31:$G$35))))))

The 1st part of the formula (<70% ) is working and returning a value.
However if the % is greater than 70% I am getting the result FALSE

I'm sure it's a simple fix but cannot put my finger on it

Any help greatly appreciated,

Thanks

 

[macropod]

Hi Mark,

Apart from anything else, you formula doesn't cater for G109=70% or G109=100%. Plus all the nested IFs seem to be pointing to the
same sum: SUM(G90-G96). Note also the odd positioning of the closing brackets for the AVERAGE('Base Data'!$I$31:$I$35 functions -
they're all at the end of the formula. It seems you're trying to nest the >70% test results inside the 'AVERAGE' function.

Perhaps you could explain what you're trying to do. Maybe something along the lines of:
=IF(G109<70%),1,IF(G109<100%,2,3))*(G90-G96)*AVERAGE('Base Data'!$G$31:$G$35)
where the 1,2,3 represent constants for the G109 % values?

 

[Bob Phillips]

Try

=(G90-G96)*(AVERAGE(IF(G109<70%,('Base
Data'!$I$31:$I$35),IF(G109<99.99%,'Base Data'!$H$31:$H$35,'Base
Data'!$G$31:$G$35))))

 

[Eduardo]

Hi Mark,
try

=IF(G109<70%,SUM(G90-G96)*AVERAGE('Base
Data'!$I$31:$I$35,IF(AND(G109>70.01%,G109<99.99%),SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109>100%,SUM(G90-G96)*AVERAGE('Base
Data'!$G$31:$G$35))))))

 

[Garreth Lombard]

Hi there,

Your missing a (OR) statement within the function. I included it for you.

=IF(G109<70%,SUM(G90-G96)*AVERAGE('Base
Data'!$I$31:$I$35,IF(or(G109>70.01%,g109<99.99%),SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109>100%,SUM(G90-G96)*AVERAGE('Base
Data'!$G$31:$G$35))))))

Hope this helps you

 

[Mark D]

Morning Macropod

Thanks for your reply.

What I am trying to do in this formula is to say (and I have simplified it
to remove the average function i had previously).

1.
IF G109 (G109 = %) is less than 70% then take G90-G96 (G90 Turnover - G96
Costs) * Base Data I36 (I36 is a % number)

BUT

IF G109 (%) is greater than 70% BUT less than 100% then take G90-G96 (G90
Turnover - G96 Costs) * Base Data H36 (H36 is a % number)

BUT
IF G109 (%) is greater than 100% then take G90-G96 (G90 Turnover - G96
Costs) * Base Data G36 (G36 is a % number)

Does that make sense what I am trying to do??

Thanks again

Hi Mark,

Apart from anything else, you formula doesn't cater for G109=70% or G109=100%. Plus all the nested IFs seem to be pointing to the
same sum: SUM(G90-G96). Note also the odd positioning of the closing brackets for the AVERAGE('Base Data'!$I$31:$I$35 functions -
they're all at the end of the formula. It seems you're trying to nest the >70% test results inside the 'AVERAGE' function.

Perhaps you could explain what you're trying to do. Maybe something along the lines of:
=IF(G109<70%),1,IF(G109<100%,2,3))*(G90-G96)*AVERAGE('Base Data'!$G$31:$G$35)
where the 1,2,3 represent constants for the G109 % values?
--
Cheers
macropod
[Microsoft MVP - Word]

Good morning everyone.

I have the following calculation that is causing me a problem

=IF(G109<70%,SUM(G90-G96)*AVERAGE('Base
Data'!$I$31:$I$35,IF(G109>70.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109>100%,SUM(G90-G96)*AVERAGE('Base
Data'!$G$31:$G$35))))))

The 1st part of the formula (<70% ) is working and returning a value.
However if the % is greater than 70% I am getting the result FALSE

I'm sure it's a simple fix but cannot put my finger on it

Any help greatly appreciated,

Thanks

.

 

[Jacob Skaria]

Try the below

=(G90-G96)*AVERAGE(OFFSET(G31:G35,,LOOKUP(G10,{0,0.7,1},{2,1,0})))

 

[Mark D]

Hi Jacob

I have entered this formula in but am getting the error dialogue box come
up. It's highlighting a problem with G96???

 

[Jacob Skaria]

Are you sure.....I missed the sheet reference..

=(G90-G96)*AVERAGE(OFFSET('Base Data'!G31:G35,,
LOOKUP(G10,{0,0.7,1},{2,1,0})))

 

[Jacob Skaria]

=(G90-G96)*AVERAGE(OFFSET('Base Data'!G31:G35,,
LOOKUP(G109,{0,0.7,1},{2,1,0})))