Need help in Droplist

[jewellove]

Hi,

I need help how to fix this formula =IFERROR(INDEX($B$12:$B$94;SMALL(IF($C$12:$C$94>=RIGHT(TRIM(LEFT(SUBSTITUTE(E$10;"-";REPT(" ";99));99));2)*1;IF($C$12:$C$94<=TRIM(RIGHT(SUBSTITUTE(E$10;"-";REPT(" ";99));99))*1;ROW($C$12:$C$94)-4));ROW(A3)));"").. It does not match the counts of student in cell C6... I like the idea of having droplist in E10. However, the criteria does not give an accurate counts. Please take a look of the attached sheet. Thanks in advance...

[Teethless mama]

Why make thing complicated:

just put score in two cells. One for lower and one for higher.

example: E10: 75, F10: 100

[jewellove]

Hello Admin,

I apologize for the duplicate post.. I thought the first was not posted as the internet was crawling. If you let me know how to delete this I will gladly comply otherwise please close.. Thank you..

[FDibbins]

@ jerwellove, i have closed your other post, so dont worry about it

[jewellove]

Hi teethless mama,
May I know how to exactly input E10: 75, F10: 100 in the worksheet. I would be grateful if you could help modify or simply the formula. It was working but I deleted some rows and it yielded wrong inputs. The formula is awesome but very complicated to repair for amateur like me.. Thank you..

[benishiryo]

hi again jewellove. 1st off, your COUNTIFS should try to cover all areas.
B3: =COUNTIFS(C11:C300,"<24")
should also included 24
=COUNTIFS(C11:C300,"<=24")

B4: =COUNTIFS(C11:C301,">=25",C11:C301,"<=49.99")
your figures have multiple decimals, so how about this instead
=COUNTIFS(C11:C301,">=25",C11:C301,"<50")

B5: =COUNTIFS(C11:C300,">50",C11:C300,"<=74.99")
again, with decimals
=COUNTIFS(C11:C300,">50",C11:C300,"<75")

B6:=COUNTIFS(C12:C301,">75",C12:C301,"<=100")
75 is not included here if you put like the above
=COUNTIFS(C12:C301,">=75",C12:C301,"<=100")

then in E12, there are a couple of mistakes:
=IFERROR(INDEX($B$12:$B$94,SMALL(IF($C$12:$C$94>=RIGHT(TRIM(LEFT(SUBSTITUTE(E$10,"-",REPT(" ",99)),99)),2)*1,IF($C$12:$C$94<=TRIM(RIGHT(SUBSTITUTE(E$10,"-",REPT(" ",99)),99))*1,ROW($C$12:$C$94)-4)),ROW(A3))),"")

the range should be from 1:95 where your last data is, or even more if you wish to.

=IFERROR(INDEX($B$12:$B$95,SMALL(IF($C$12:$C$95>=RIGHT(TRIM(LEFT(SUBSTITUTE(E$10,"-",REPT(" ",99)),99)),2)*1,IF($C$12:$C$95<=TRIM(RIGHT(SUBSTITUTE(E$10,"-",REPT(" ",99)),99))*1,ROW($C$12:$C$95)-4)),ROW(A3))),"")

remember i explained about wanting to converting ROW 12-95 to 1-84 instead. to do that, you must minus the number of rows above. it's minus 11 in this case, not 4. and i want to return the 1st smallest number, so it's ROW(A1), not ROW(A3).

so the final formula is:

Please Login or Register  to view this content.

[jewellove]

Benishiryo,


Thank you for the many many helps from yesterday until now. I have been working on this since friday.It was holiday here last friday.. I could not construct a worksheet dynamic during office days as I will be busy data entry which probably I will no longer do next week as most of my worksheets can compute automatically now. Prior to being a member of this forum, I could not get most formula work and I almost gave up .. Thank you again

I have uploaded the updated worksheet with your formula inputted. It can't yield numerical result tho.. Please advice.. Thank you..

[jewellove]

Thank you for the advise inbox. I already removed the space and modified the countif formula. There is result now result for E12 but it does not follow the drop button criteria.

[jewellove]

The formula works.. I pressed ctlr+shift+enter. This thread is solved.. YAHOOOOOOOOOOOOOOOOOOOOOOO
Thank so very much everyone