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.

Number	Result		Days		Output
35	1-2		0	67	1-2
85	3-4		67	145	3-4
172	7-8		145	172	5-6
200	9-10		172	184	7-8
50	1-2		184		9-10
20	1-2
184	9-10

<tbody>
</tbody>

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!