Flag Outliers in an Excel Datafile Before Export

[cool2021]

How do I create a new column of data in an Excel datafile flagging outlier values and then export that datafile so I can do more advanced modeling or analysis in SAS or SPSS?

BUT....just using the conditional formatting colour scaling in Excel will not work since colour scales formats do not export into a .csv or .txt file that I would then import into SAS.

I need to export a datafile from Excel - either Web or Desktop - so I can bring it into SAS or SPSS to do some further analysis. But, before I do, I need to have a new column of data in my Excel file that flags records that are outliers - unusually high or low data values - based on one column of $ Average Order Amount data in my file.

[#2]

Hi @cool2021 

you could use a formula in a separate column that flags values based on a rule that you need to define.

This example would flag values below 80% or above 120% of the average with *

=IF(OR(A2<AVERAGE($A$2:$A$25)*80%,A2>AVERAGE($A$2:$A$25)*120%),"*","")

Is that what you need?

[#3]

@Martin_Weiss thanks for the response. No, I actually want to do this without having any formulas in cells. But thanks for the response anyway.

[#4]

Hi @cool2021 

but you wrote that you would need a new column with the flags, because conditional formatting obviousely does not get exported to a CSV.

So what exactly would you like to achieve?

[#5]

@Martin_Weiss that is okay. I figured it out. I used a VBA Macro to create the new column of data with a flag (eg. High Outlier, Normal, Low Outlier) for each record without having any formulas in the spreadsheet cells. Then, I could just export to .csv.