Hi,
I have a list of questions in column B S1, S2, S3 etc. Each question has different scoring underneath - 0,1,2,3 or -1,0,1 etc etc - I need a formula to search for the question "S1" and return the maximum score. I am using
at the moment but this searches the whole list of scores below question S1 and not just those within that particular question.
Please Login or Register to view this content.
Is there any way to search until there is a blank?
Thanks for any help,
Henry.
pls provide sample excel file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Max example.xlsx
As attached.
B29=iferror(max(offset($a$1,match($a29,$a$1:$a$26,0)-1,0,sumproduct((lookup(row($a$1:$a$26),row($a$1:$a$26)/istext($a$1:$a$26),$a$1:$a$26)=$a29)*1)-1,1)),"")
c29=iferror(min(offset($a$1,match($a29,$a$1:$a$26,0)-1,0,sumproduct((lookup(row($a$1:$a$26),row($a$1:$a$26)/istext($a$1:$a$26),$a$1:$a$26)=$a29)*1)-1,1)),"")
An alternative, with an easier-to-maintain formula, involves a little rearrangement of your data (which may, or may not, be possible).
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Siva, your solution is incorrect for the last MAX value...
Thanks to both for your answers. Unfortunately, Glenn, I can't rearrange the data as it's a set-formatted list of questions.
Also, the array just needs to be lengthened by one cell for Siva's formula to work.
Yes your right Glenn
re attaching the file
thank you hc91 for your feedback and adding reputation as glen said it has a flaw i rectified in the above attached file
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks