Corrupt excel sheet or problem with the formula?

[oxygn]

Hi All,

I have trouble with excel sheet or the formula. I am trying to calculate Min with multiple conditions. The formula I am using is {=MIN(IF(Tier=3,IF(Location="USA",IF(LCount<>"",LCount))))}. If I use the formula on other sheets it works find but on this particular sheet it returns the wrong answer. I have attached the sheet to look at: Droplr • Sample.xlsx

Region	Tier	Location Count
Japan	3
USA	3	50		Min =	0
Japan	3				Answer should be 26 and not 0 as above
USA	3	56
Japan	3
USA	3	696
Australia	3
Australia	3
USA	3	306
USA	3	26

<tbody>
</tbody>

I tried using Clean function to clean the cells but nothing happened. I also tried deleting named ranges and re adding them still no luck.

Please help.
Regards,

 

[tweedle]

I didn't change a thing and came back with the expected result.
Make sure your Locations don't have trailing spaces (I assume that's what your 'Clean'ed.)
F9, to ensure you calculate.

*	A	B	C	D	E	F
5	Region	Tier	Location Count	*	*	*
6	Japan	3	*	*	*	*
7	USA	3	50	*	Min =	26
8	Japan	3	*	*	*	*
9	USA	3	56	*	*	*
10	Japan	3	*	*	*	*
11	USA	3	696	*	*	*
12	Australia	3	*	*	*	*
13	Australia	3	*	*	*	*
14	USA	3	306	*	*	*
15	USA	3	26	*	*	*

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:100px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F7 =MIN(IF(Tier=3,IF(Location="USA",IF(LCount<>"",LCount)))) <tbody> </tbody>

Names in Formulas Cell Name Applies to F7 LCount =Sheet3!$C$6:$C$15 F7 Location =Sheet3!$A$6:$A$15 F7 Tier =Sheet3!$B$6:$B$15 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[oxygn]

Thanks tweedle ,

I thought there was some problem with meta data. I copied all the data to a new sheet with paste special and checked the formula. It worked

So I moved it back, renamed the sheet and now everything is perfect.

Thanks,
K