Commentary and If statements

[goldfield]

Hi Gurus,

I am looking to create some automated reports from data in a spreadsheet and was looking for some assistance.

I am concatenating text and data to form the reports and would like to base that commentary on data in cells.
ie- If the total sales have increased this month then list the drivers of the increase.

eg Total Sales increased 5% this month and were driven by an increase of 6% in car sales and a decrease of 1% in bike sales.

Conversely, i would like to put the bike sales first if they were causing the increase.

eg Total sales increased 5% this month and were driven by an increase of 7% in bike sales and a decrease of 2% in car sales.

I am fine with identifying the increase/decrease using isnumber(search), but am struggling to find a way to have excel report in the correct order.

Hope this makes sense.

Cheers,

G

[FlameRetired]

In the attached I have changed the source figures to demonstrate that this is working.

To determine Total increase/decrease please find in B4

Please Login or Register  to view this content.

Then in C4

Please Login or Register  to view this content.

From there the simplest way I can think of is to use a row of helper cells as in E2:M2.

In E2

Please Login or Register  to view this content.

To resolve the "a"/"an" in F2

Please Login or Register  to view this content.

In G2

Please Login or Register  to view this content.

H2

Please Login or Register  to view this content.

I2

Please Login or Register  to view this content.

J2

Please Login or Register  to view this content.

K2

Please Login or Register  to view this content.

L2

Please Login or Register  to view this content.

M2

Please Login or Register  to view this content.

Then in D5 a referencing series of concatenations nested in the commentary.

Please Login or Register  to view this content.

[goldfield]

Thanks for the response- this is fantastic!
Just one other thing, it looks like some of the data is actually going to have negative numbers, which from what I can work out will not work with Min/Max.
Can you suggest a solution or workaround for this?

[FlameRetired]

Not sure I am interpreting correctly.

Please upload another sample in your next post that demonstrates what you describe along with desired output. (please don't edit/change the first one)

[goldfield]

Hi Flameretired,
I have added a new sample which hopefully highlights the issue.
Thanks again!

[FlameRetired]

I am somewhat confused by the upload cell comments although the output string in the attached matches your expected string.

The helpers:
Formula in E2 remains the same.

The rest must be array entered.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

In F2

Please Login or Register  to view this content.

In G2

Please Login or Register  to view this content.

In H2

Please Login or Register  to view this content.

In I2

Please Login or Register  to view this content.

In J2

Please Login or Register  to view this content.

In K2

Please Login or Register  to view this content.

In L2

Please Login or Register  to view this content.

In M2

Please Login or Register  to view this content.

The final formula remains the same as before.

[goldfield]

Thanks FlameRetired!
Much appreciated

[FlameRetired]

You are welcome. Thanks for the feedback and marking your thread Solved.