Changing the order of text based on results

[amartino44]

Hello. I've attached a spreadsheet. I want the order of the sentence to change based on the numbers and whether they are positive or negative. Below are more details and the spreadsheet has numbers. Thanks



"Total expense for September was 8 higher (lower) than prior year, driven by a higher (lower) claims expense and admin expense"


This might be hard to explain: For the second part (starting from "driven by", I would like the variable that explains more of the positive or negative variance (in the example, claims) to come first.

Here is a different example

Total expense for September was 6 lower than prior year, driven by a lower admin expense (claims is not included because it is higher)

Does this make sense?

Thanks

[TonyB51]

Hi,

I think I achieved what you may have been looking for.

Check out your amended spreadsheet attached.

Cheers

TonyB

[amartino44]

@TonyB51. That was helpful but not what I need. You only include "higher claims expense and admin expense" and "lower claims expense"...There are way more possibilities than that. Examples below:

Higher...Higher claims expense (admin is lower), Higher admin expense (claims is lower) Higher claims and admin expense (both are higher, but claims is the highest), and higher admin expense and claims expense (both are higher, but admin is the highest)

Lower...Lower claims expense (admin is higher) etc.

[FDibbins]

amartin, this is the pretty much a continuation of your other thread...
http://www.excelforum.com/excel-gene...ml#post3429497

If you have a list of phrases, and some way to "pick" which 1 to use, when, you could probably use a vlookup inside the formula I suggested in your other post (which, by the way, you still have not commented on?)

[amartino44]

It's similar to the other thread, but the idea is much different. It has to do with order of the phrasing.

[FDibbins]

Yes, I get that

Did you read the 2nd part of my reply?

[amartino44]

I did but I didn't understand it. Could you give me an example that would be applicable to the situation above?

[FDibbins]

Total expense for September was 6 lower than prior year, driven by a lower admin expense (claims is not included because it is higher)

I presume your "variable" text in that is "driven by...etc", and that you will have a few more like that? When will you know which 1 you want to use - what will be the criteria that will "choose" which text to insert?

[amartino44]

@F Dibbins. Incorrect. Driven by is a constant. Here are the variables (in CAPS)

"Total expense for SEPTEMBER was 6 HIGHER/LOWER than prior year, driven by by a HIGHER/LOWER CLAIMS EXPENSE, ADMIN EXPENSE, AND DAC.

The first part is fairly easy, I just reference a cell with the month and then based on a value in a cell, I do an if statement for value in cell and Higher/Lower based on that cell value. The second part is REALLY hard. If the first part was "higher", I want only the variables with positive values in the adjacent cell AND I want them to be in order from highest to lowest. If the first part was "lower", I want only the variables with negative values in the adjacent cell AND I want them to be in order from most negative to least negative.

Example:
Main variable: 10
Claims: 4
Admin: 10
DAC: -4

Sentence: Total expense for September was 10 higher than prior year, driven by a higher admin and claims expense