Generate Summary Paragraphs

[KrisKiel]

I'm trying to design a sheet that generates a standardized summary of the results of an assessment. I've begun making formulas to do this. However, I would prefer the summary generate as formatted paragraphs, rather than as single lines as it does when written as formulas.

Here's an example of one of these formulas for a single item (with the text shortened for brevity), but there are easily going to be 100 such IFS statements.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IFS('Assessment Score Summary'!D20<67,"1-2 years",'Assessment Score Summary'!D20<145,"3-4 years",'Assessment Score Summary'!D20<172,"5-6 years",'Assessment Score Summary'!D20<184,"7-8 years",'Assessment Score Summary'!D20>183,"9-10 years")[/FONT]

How can I make this happen? Is this something that is achievable with VBA? If it is, how do I string multiple such statements together into paragraphs?

 

[KrisKiel]

I realize I might be a bit all over the place. The essential here is to have the multiple statements put together into neat paragraphs that can be exported as a PDF.
​

 

[levy77]

You could look at having a sheet with all your return values then use a lookup function. As per the example below.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD]Days[/TD]
[TD][/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]1-2
[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]67[/TD]
[TD]1-2[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]3-4[/TD]
[TD][/TD]
[TD]67[/TD]
[TD]145[/TD]
[TD]3-4[/TD]
[/TR]
[TR]
[TD]172[/TD]
[TD]7-8[/TD]
[TD][/TD]
[TD]145[/TD]
[TD]172[/TD]
[TD]5-6[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]9-10[/TD]
[TD][/TD]
[TD]172[/TD]
[TD]184[/TD]
[TD]7-8[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]1-2[/TD]
[TD][/TD]
[TD]184[/TD]
[TD][/TD]
[TD]9-10[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]1-2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]184[/TD]
[TD]9-10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula to add into B2 and drag down is =LOOKUP($A2,$D$2:$E$6,$F$2:$F$6)

Is this sort of what you are looking for?

 

[KrisKiel]

I was trying to keep the If True text brief since that's not so important except on the finished product. What that single formula looks like in whole is this:

=IFS('Assessment Score Summary'!D20<67,"On the DT module, overall factor score was comparable to the functioning level of a 1- to 2-year-old learner.",'Assessment Score Summary'!D20<145,"On the DT module, overall factor score was comparable to the functioning level of a 3- to 4-year-old learner",'Assessment Score Summary'!D20<172,"On the DT module, overall factor score was comparable to the functioning level of a 5- to 6-year-old learner.",'Assessment Score Summary'!D20<184,"On the DT module, overall factor score was comparable to the functioning level of a 7- to 8-year-old learner",'Assessment Score Summary'!D20>183,"On the DT module, overall factor score was at the highest level of functioning in this module, assessed to the functioning level of a 9- to 10-year-old learner.")

And like I said, there are going to be about 100 of these statements. I want users to be able to complete the assessment (which is separate from this post), and have a written report

(sentences and paragraphs)​

automatically generate for them describing the results.

 

[Twollaston]

I was trying to keep the If True text brief since that's not so important except on the finished product. What that single formula looks like in whole is this:

=IFS('Assessment Score Summary'!D20<67,"On the DT module, overall factor score was comparable to the functioning level of a 1- to 2-year-old learner.",'Assessment Score Summary'!D20<145,"On the DT module, overall factor score was comparable to the functioning level of a 3- to 4-year-old learner",'Assessment Score Summary'!D20<172,"On the DT module, overall factor score was comparable to the functioning level of a 5- to 6-year-old learner.",'Assessment Score Summary'!D20<184,"On the DT module, overall factor score was comparable to the functioning level of a 7- to 8-year-old learner",'Assessment Score Summary'!D20>183,"On the DT module, overall factor score was at the highest level of functioning in this module, assessed to the functioning level of a 9- to 10-year-old learner.")

And like I said, there are going to be about 100 of these statements. I want users to be able to complete the assessment (which is separate from this post), and have a written report

(sentences and paragraphs)​

automatically generate for them describing the results.

you can use something like this where the paragraph should go
=TEXTJOIN(" ",TRUE,Y8:Z8)
and where it says Y8:Z8 you can just use the cells with the text you want to use.

 

[KrisKiel]

Oh my gosh I feel like an idiot. That was the simplest thing ever. Thank you.

 

[Twollaston]

No problem, happy to help!