Sorting Output with Formulas in It

**mycon73** · 01-03-2009, 02:42 AM

Hi Excel Users,

I have a formula that looks like the following & works as it should:

=IF(G16="TC","TC",IF(G16="--","PEN",IF(F16<=0,"",IF(F16<=TODAY()+5,"A",IF(F16>TODAY()+16,"C",IF(F16>=TODAY()+6,"B",IF(F16<=TODAY()+15,"B")))))))

However, I would like to sort the output - A,B,C, etc. in ascending or descending order.

However, the way my formula appears to be set up, instead of sorting the desired output, it's sorting the 'i' in the IF formula instead.

How can sort the calculate output, without changing the formula, when I select the Ascending or Descenting options? If necessary, I can or will change my formula, but at the moment, for it's main other purpose, it works well.

Thanks

**clownfish** · 01-03-2009, 02:53 AM

You need to highlight *all* your data and sort it based on the formula column! For instance, if your formulas are in Column E and the rest of the data is in Cols F and G, you highlight E, F, & G and sort based on E. Obviously, if you also have related data in Columns A:D or beyond Column G, you will need to include that too or you will have a small disaster on your hands.

**mycon73** · 01-04-2009, 03:10 PM

Hi Clownfish,

Thanks for replying. However, I'm not quite understanding on you take *all* & sort the desired columns with formulas in it.

Another thing that I didn't state before. In the column, which has the formula in it, sometimes produces no results, as it should be because there's no data to sort in a particular row. Therefore, the column has A,B,C results & "blank" or "empty" results.

When attempting to sor the column, the 'blank' or 'empty rows are being moved to the top, even though it's suppose to follow the A,B,C ascending order.

How do I get this columm to sort properly?

Thanks again

Originally Posted by clownfish

You need to highlight *all* your data and sort it based on the formula column! For instance, if your formulas are in Column E and the rest of the data is in Cols F and G, you highlight E, F, & G and sort based on E. Obviously, if you also have related data in Columns A:D or beyond Column G, you will need to include that too or you will have a small disaster on your hands.

**shg** · 01-04-2009, 04:07 PM

Post a workbook that illustrates what you're trying to do?

**clownfish** · 01-05-2009, 12:27 AM

I'm assuming your first problem is fixed now-- the column with formulas is now sorting (at least in some fashion) based on the "ouput" of the formulas rather than on "=IF(..." or whatever?

About the other problem, I think Excel sees this: "" as the "highest" text value and it will therefore outrank any other text, which would explain why your blanks are appearing at the top of your sorted list when you sort ascending.

There's no great way to get around this, but here are a couple of different hacks:

replace the "" in your IF statement with CHAR(158), as in =IF(A2>55,B2+C2,CHAR(158))
instead of
=IF(A2>55,B2+C2,"")
Character 158 appears to be something like a Czech "z", and will always be sorted to the bottom of the list. If it's annoying to you to have anything appearing in the cells that should be blank, you can do this:

replace the "" in your IF statement with "zzz" and use conditional formatting to make cells in this column that are equal to zzz have the same background and text color (white/white, for instance), which will effectively make them invisible (although they still will look a little odd, because the default cell color in Excel is not actually white, but transparent).

Somebody else probably has a prettier solution than this!

Sorting Output with Formulas in It

LinkBack

Thread Tools

Rate This Thread

Display

Sorting Output with Formulas in It

Sorting Columns with Formulas In It

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions