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

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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]


"Mark D" wrote in message ...
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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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))))

--

HTH

Bob

"Mark D" wrote in message
...
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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

Hi Mark,
try

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


"Mark D" wrote:

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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(G10970.01%,g109<99.99%),S UM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,SUM(G90-G96)*AVERAGE('Base
Data'!$G$31:$G$35))))))

Hope this helps you

--

Thank you and Regards

Garreth Lombard


"Mark D" wrote:

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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

"macropod" wrote:

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]


"Mark D" wrote in message ...
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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

.

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

Try the below

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

--
Jacob


"Mark D" wrote:

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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" wrote:

Try the below

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

--
Jacob


"Mark D" wrote:

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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


"Mark D" wrote:

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" wrote:

Try the below

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

--
Jacob


"Mark D" wrote:

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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

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

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

--
Jacob


"Mark D" wrote:

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" wrote:

Try the below

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

--
Jacob


"Mark D" wrote:

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(G10970.01%<99.99%,SUM(G90-G96)*AVERAGE('Base
Data'!$H$31:$H$35,IF(G109100%,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