Ask the Bear & Badger - The Bear and Badger

tigersgogrrr

That's good to know.

I'm learning!

cockbeard

acemuzzy wrote:

Stay with just the first IF and debug from there.. ie IF(H5>79%,"S","X"). And build from there.

Isn't the first IF, "IF(H5>34,”B+”,”B”)", just that's where I start checktesting from anyway

Also yeah lookup would be better, too much business logic in the equation, pain if yo utweak the percentage/grade relationship later down the line or for other subjects

tigersgogrrr

So how would that look using lookup? I see how it would work for a particular number but not for a range?

cockbeard

+---------+--------------+-------+-------+
| subject | grade        | lower | upper |
+---------+--------------+-------+-------+
| math    | a            | 95    | 100   |
+---------+--------------+-------+-------+
| math    | b            | 85    | 95    |
+---------+--------------+-------+-------+
| math    | c            | 75    | 85    |
+---------+--------------+-------+-------+
| math    | d            | 0     | 75    |
+---------+--------------+-------+-------+
| english | dist         | 90    | 100   |
+---------+--------------+-------+-------+
| english | commendation | 75    | 90    |
+---------+--------------+-------+-------+
| english | pass         | 65    | 75    |
+---------+--------------+-------+-------+
| english | remedial     | 0     | 65    |
+---------+--------------+-------+-------+

cockbeard

I'm no excel whizz but I'm pretty sure VLOOKUP works with boundary ranges nowadays, but you might need to split out the subjects into individually defined lookup tables, these boundaries could be generated dynamically so they are still valid when other details are added or removed

acemuzzy

I think there's a fourth param that determines the handling. False means exact, true rounds, or something like that?

cockbeard

I haven't touched in a while the only thing O'm sure I remember about it is that the lookup has to be ordered by the bounded range ascending. Which is why my example above with all subjects and bounds might fall over if you select the whole thing as the lookup, so could dynamically select the part you want

for me it's as simple as "SELECT grade WHERE subject=subjact AND mark BETWEEN upper AND lower"

tigersgogrrr

So, I've created a table similar to Cocko's above. Three columns: Grade, Lower %, Upper Percentage. What would be my command to choose a band that lies within the two?

=VLOOKUP(H7,K19:M23,1,TRUE)

Is what I'm using currently - H7 is where the calculated % lies. K19 is the upper left of the columns, M23 the lower right, 1 is the column where the Band would be take from.

Sorry if that seems like gibberish. This is pretty much the first time I'd done this.

EDIT: Might have found the solution...

acemuzzy

I think you want lower percentage and then grade as your two columns, in that order. And then vlookup(range, mark, 2, true).

acemuzzy

Maybe upper percentage rather than lower if that gives you the wrong grade (by one). But you shouldn't need both.

tigersgogrrr

Ok, formula is...

=VLOOKUP(H7,K19:L23,2, TRUE)

K19 - L23 covers the table. Percentage taken from H7. 2 is the column with the letters I want to display.

It's only displaying the top result (um, the lowest result, at the top of the table).

Someone just come to my house and do this for me bleughueuhg.

tigersgogrrr

Woo, fixed it. I was being an idiot - Using percentages but not change my numbers to 0.8 etc.

acemuzzy

Cool! I think you can write "80%" rather than "0.8". Or you can change the cell formatting to be % maybe? Might look more readable...

regmcfly

London Forum Assemble
Flora and I are booked to go to London from 3-7 April as our last proper holiday before D-Day.
We are booked into The Book if Mormon on the Saturday Night and Matilda on Sunday matinee, and want to go back to Bar Shu, recommended to us by Jay Rayner, for Szechuan.

Aside from that, we are looking for food recommendations. We are staying on Brick Lane as we love that area. Probably things that don't rely on nice drinks as flora will be 6 months at that point. If anyone can recommend cool places (I'm thinking like when fullspec took us to Duck and Waffle) please let me know.

Tempy

So you’re going to call the kid Dylan?

regmcfly

Ayo

nick_md

Kanada Ya for ramen by Denmark st, the Japanese I was seeing claimed it best ramen in London, always had bare Asian salrarymen in.

LivDiv

If you catch yourself short and want something quick you have to go to Beigel on Brick Lane.
24 hours, traditional bagels, legendary.

nick_md

Japes in Soho isn't fancy but their deep dish pizza is a gluttonous pleasure.

regmcfly

LivDiv wrote:

If you catch yourself short and want something quick you have to go to Beigel on Brick Lane.
24 hours, traditional bagels, legendary.

This one is on the list already. I believe Eater had it mentioned as like £5 for the best salt beef on the planet.

regmcfly

nick_md wrote:

Kanada Ya for ramen by Denmark st, the Japanese I was seeing claimed it best ramen in London, always had bare Asian salrarymen in.

Is this better than the ramen joint that did their own noodles? We went there about a decade ago and it was... Alright.

regmcfly

I think it was called Tonkatsu

Diluted Dante

There are 5 Wimpys in London.

nick_md

The Heron pub near Edgware road doesn't look like the place you'd find the best Thai food in London, but it is; again, a Laotian friend took me there with other Asians and you go downstairs from what looks like a fairly regular pub, to find a restaurant filled with Asians, Thai TV on all the screens, that smell of fish sauce hanging in the air. The food was amazing.

nick_md

Diluted Dante wrote:

There are 5 Wimpys in London.

One is down the road from me, I should visit.

nick_md

regmcfly wrote:

I think it was called Tonkatsu

Not sure tbh but the queue of Asians outside Kanada Ya before they open (considering it's not in a known restaurant location) tells you it's good.

nick_md

Disclaimer, I've been there twice and not for about 4 years.

LivDiv

regmcfly wrote:

LivDiv wrote:

If you catch yourself short and want something quick you have to go to Beigel on Brick Lane.
24 hours, traditional bagels, legendary.

This one is on the list already. I believe Eater had it mentioned as like £5 for the best salt beef on the planet.

I've only had it drunk at the end of the night, not the best way to fully appreciate it but I remember it being pretty special.
They come up on a lot of lists and shows.

nick_md

I've had them bagels, proper nice.

yourfavouriteuncle

Me too but then I’ve also been for the best bagels in the uk up in Regs neck of the woods so what’s he going to make of them? It’s a big ol bagel off, i reckon.

Howdy, Stranger!

Categories