Graphs-formula to use

AnaFab · Nov 13, 2023

Hi
I would really appreciate some help as my knowledge in excel is basic.
I have created a questionnaire with 11 questions and this will be completed at 2 different times with each person, once pre and once post intervention.
The achieves for each question are 1 to 10. When I get the results, I am aiming to see if the scores have improved post intervention. Now, I can do graphs that show the scores for each question but what I really want to show is that how many people (in percentage) showed an increase in each question post intervention. Does that make sense? So rather than the total scores, say 80% of people show an improvement in family life post intervention (as question 1 in time 2 were greater scores than in time 1). I hope that makes sense

ExceLoki · Nov 13, 2023

i think this is what you're asking for, if not let me know...
in line 14 it's showing % of each person that increased from time 1 to time 2 for each question
----------------

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

person

question 1 time 1

question 1 time 2

question 2 time 1

question 2 time 2

question 3 time 1

question 3 time 2

question 4 time 1

question 4 time 2

question 5 time 1

question 5 time 2

question 6 time 1

question 6 time 2

question 7 time 1

question 7 time 2

question 8 time 1

question 8 time 2

question 9 time 1

question 9 time 2

question 10 time 1

question 10 time 2

question 11 time 1

question 11 time 2

2

person 1

5

6

1

6

5

4

5

8

4

8

1

4

3

8

4

7

6

5

2

10

6

4

3

person 2

3

6

4

10

1

9

3

6

4

8

5

3

6

4

6

5

6

5

8

4

person 3

3

10

4

5

3

4

2

6

5

9

1

8

3

7

2

9

1

6

2

4

8

5

person 4

2

4

3

7

6

10

2

8

3

6

4

2

8

4

7

5

7

3

10

7

10

6

person 5

2

10

3

7

1

7

5

4

1

8

6

4

5

1

7

5

7

4

10

6

7

person 6

4

6

4

1

9

1

8

2

9

1

5

4

8

6

8

2

7

1

9

6

5

8

person 7

4

9

2

10

1

6

2

6

2

6

4

10

6

5

6

8

2

7

3

9

6

10

9

person 8

1

10

2

6

3

4

10

3

7

6

10

2

8

5

10

4

9

2

10

7

10

person 9

6

8

5

9

5

10

6

7

2

4

6

9

4

6

10

4

6

8

11

person 10

6

5

3

5

3

10

3

4

5

7

5

8

4

7

4

10

6

8

3

9

7

8

12

13

question 1

question 2

question 3

question 4

question 5

question 6

question 7

question 8

question 9

question 10

question 11

14

Sheet1

Cell Formulas
Range	Formula
B14,V14,T14,R14,P14,N14,L14,J14,H14,F14,D14	B14	=COUNTA(FILTER($A$2:$A$11,C2:C11>B2:B11))/COUNTA($A$2:$A$11)

AnaFab · Nov 13, 2023

ExceLoki said:
i think this is what you're asking for, if not let me know...
in line 14 it's showing % of each person that increased from time 1 to time 2 for each question
----------------

Book1
A B C D E F G H I J K L M N O P Q R S T U V W
1 person question 1 time 1 question 1 time 2 question 2 time 1 question 2 time 2 question 3 time 1 question 3 time 2 question 4 time 1 question 4 time 2 question 5 time 1 question 5 time 2 question 6 time 1 question 6 time 2 question 7 time 1 question 7 time 2 question 8 time 1 question 8 time 2 question 9 time 1 question 9 time 2 question 10 time 1 question 10 time 2 question 11 time 1 question 11 time 2
2 person 1 5 6 1 6 5 4 5 8 4 8 1 4 3 8 4 7 6 5 2 10 6 4
3 person 2 3 6 4 10 1 9 3 6 6 6 4 8 5 5 3 6 4 6 5 6 5 8
4 person 3 3 10 4 5 3 4 2 6 5 9 1 8 3 7 2 9 1 6 2 4 4 8
5 person 4 2 4 3 7 6 10 2 8 3 6 6 4 2 8 4 7 5 7 3 10 7 10
6 person 5 2 10 3 7 1 7 5 4 1 8 6 6 4 5 1 7 5 7 4 10 6 7
7 person 6 4 4 6 4 1 9 1 8 2 9 1 5 4 8 6 8 2 7 1 9 6 5
8 person 7 4 9 2 10 1 6 2 6 2 6 4 10 6 5 6 8 2 7 3 9 6 10
9 person 8 1 10 2 6 3 4 4 10 3 7 6 10 2 8 5 10 4 9 2 10 7 10
10 person 9 6 8 5 9 5 10 6 7 2 4 4 4 6 9 4 4 6 10 4 6 6 8
11 person 10 6 5 3 5 3 10 3 4 5 7 5 8 4 7 4 10 6 8 3 9 7 8
12
13 question 1 question 2 question 3 question 4 question 5 question 6 question 7 question 8 question 9 question 10 question 11
14 80% 90% 90% 90% 90% 70% 80% 90% 90% 100% 80%
Sheet1
Cell Formulas
Range Formula
B14,V14,T14,R14,P14,N14,L14,J14,H14,F14,D14 B14 =COUNTA(FILTER($A$2:$A$11,C2:C11>B2:B11))/COUNTA($A$2:$A$11)

I think this is it!! I shall try! Thank you

ExceLoki · Nov 13, 2023

happy to help, lemme know if it works for your needs

AnaFab · Nov 14, 2023

Hi. Thank you so much for your support. It’s not quite working as I wanted but so close. What could I be doing wrong?

ExceLoki · Nov 14, 2023

can you share a sample to see what you're working with?
suggest using xl2bb plugin, link below

AnaFab · Nov 14, 2023

ExceLoki said:
can you share a sample to see what you're working with?
suggest using xl2bb plugin, link below

Excel Formula:

I am struggling to do this. I will ask for help later in the day to see if I can upload it. Thank you for your patience and time.

AnaFab · Nov 15, 2023

My work does not allow me to download xl2bb. I will see if I can borrow a laptop to do this. So frustrating. Thank you for your help

Graphs-formula to use

AnaFab

New Member

Excel Facts

ExceLoki

Well-known Member

AnaFab

New Member

ExceLoki

Well-known Member

AnaFab

New Member

ExceLoki

Well-known Member

AnaFab

New Member

AnaFab

New Member

Similar threads

Forum statistics

Share this page

Graphs-formula to use

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock