Dynamic Named Range with IF Condition

[Amit Gandhi]

Hi Experts

I have created 2 Dynamic Named Ranges using OFFSET formula as below for plotting on Chart.

PricesSeries
LastPrice


Now, I want to create 3rd Dynamic Named Range using IF Condition on above 2 Series.

Formula is:
=IF(AND((PricesSeries >= (99.95% * LastPrice)), (PricesSeries <= 100.05% * LastPrice))), PriceSeries , 0 )


Please advise how to do it.

I have attached excel file which shows sample data and result required for better understanding.

Please help me here.

Regards

Amit

 

[Hui]

Amit

This is a great question

Firstly, you have a spelling mistake in your formula above:

Formula should be:
=IF(AND((PricesSeries >= (99.95% * LastPrice)), (PricesSeries <= 100.05% * LastPrice))), PricesSeries , 0 )

However that won't solve the issue

Try: =IF(PricesSeries >= 99.95% * LastPrice, IF( PricesSeries <= 100.05% * LastPrice,PricesSeries, 0),0)

It returns: ={638.4;0;0;638.2;0;0;638.1;0;0;0;638.7;0;638.45;0;638.5}

Note that you have to avoid using the AND, as it doesn't return an array as a result
So instead simply stack IF statements

 

[Amit Gandhi]

Amit

This is a great question

Firstly, you have a spelling mistake in your formula above:

Formula should be:
=IF(AND((PricesSeries >= (99.95% * LastPrice)), (PricesSeries <= 100.05% * LastPrice))), PricesSeries , 0 )

However that won't solve the issue

Try: =IF(PricesSeries >= 99.95% * LastPrice, IF( PricesSeries <= 100.05% * LastPrice,PricesSeries, 0),0)

It returns: ={638.4;0;0;638.2;0;0;638.1;0;0;0;638.7;0;638.45;0;638.5}

Note that you have to avoid using the AND, as it doesn't return an array as a result
So instead simply stack IF statements

Thanks Hui
It perfectly fits.