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
[TABLE="width: 762"]
<tbody>[TR]
[TD]Region
[/TD]
[TD]Tier[/TD]
[TD]Location Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Japan[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]3[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]Min =[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Japan[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Answer should be 26 and not 0 as above[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]3[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Japan[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]3[/TD]
[TD]696[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]3[/TD]
[TD]306[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]3[/TD]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
Region	Tier	Location Count	*	*	*
Japan	*	*	*	*
USA	*	Min =
Japan	*	*	*	*
USA	*	*	*
Japan	*	*	*	*
USA	*	*	*
Australia	*	*	*	*
Australia	*	*	*	*
USA	*	*	*
USA	*	*	*

<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>
[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]56[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]696[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]306[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]26[/TD]

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