Excel 2003 to 2010 help please

[Colbaker]

Hi all,

My company has updated Excel to 2010 from 2003, now when creating a few formula's with Excel 2003 i found that there were easyier ways to do it in 2010 but cannot remember what these ways where, Below is a list of the different formula's, is there any changes i can make to simplify them using the updates in 2010?

=IF(ISNA(VLOOKUP(F2,ALIGNMENT,4,FALSE)),"",VLOOKUP(F2,ALIGNMENT,4,FALSE))

=(SUMPRODUCT(--(Tracker!E2:E1000="LANDROVER"),--(Tracker!T2:T1000="YES")/COUNTIF(Tracker!E2:E1000,"LANDROVER")))


=B3-SUMPRODUCT(--(Tracker!E2:E1000="LANDROVER"),--(Tracker!P21000=""))

I think in those last 2 i should be able to use COUNTIFS which would simplify things.

What do you think or are these ok and just to leave them alone?

 

[andshrew]

What do you think or are these ok and just to leave them alone?

They're not going to stop working in 2010 so I would be tempted to leave them as they are.

But for the bottom two generally speaking you can use COUNTIFS. If a less experienced Excel user had to maintain the spreadsheet in future they may find it easier to understand if you updated it to use these due to the formula being simpler.

 

[Colbaker]

They're not going to stop working in 2010 so I would be tempted to leave them as they are.

But for the bottom two generally speaking you can use COUNTIFS. If a less experienced Excel user had to maintain the spreadsheet in future they may find it easier to understand if you updated it to use these due to the formula being simpler.

That is exactly the reason why I was thinking about changing them to COUNTIFS, also I can now use the ISERROR formula cant I?

 

[andshrew]

ISERROR would work but actually being more specific about the type of error your looking for may be more useful. ie. if it's N/A you know the VLOOKUP has worked but it hasn't found a matching value. If you change your IF to say that any returned error is acceptable you could end up masking an actual error should one occur which may end up being difficult to track down.

 

[Colbaker]

What am I doing wrong?, I am trying to convert the below to using COUNTIFS but get the #VALUE error

=(SUMPRODUCT(--(Tracker!E2:E1000="LANDROVER"),--(Tracker!T2:T1000="YES")/COUNTIF(Tracker!E2:E1000,"LANDROVER")))

What I am doing is...

=COUNTIFS (Tracker!E2:E1000,"LANDROVER", Tracker! T2:T1000,"YES")/COUNTIF (Tracker! E2:E1000,"LANDROVER"))

Help please