output a result based on 3 variables

Ummm. What do you expect to see? Where do you expect to see it? How does the manual calculation work?

Remember... you're very familiar with this. I've been looking at it only for a minute and cannot see what is needed, or where!!

Apologies, as it is my first post after finding the forum, it has been very useful!

I am hoping to output a result that is a 1-5 range based on the tables that would be excellence being 5, gold 4, silver 3, bronze 2, participated as 1. null if no value.


I don't need it somewhere specific as I can move it around but the output eventually would be the 1-5 range under each fitness activity column on the sheet called auto badge calc which would then see the number and do more after to follow the rules for calculating the badges (already in place).

manual calculation are based on the data entry on the sheet called data and result entry, after cadets complete an activity they submit how many to the staff who though paperwork eventually enter the data to this spreadsheet but the final goal is to tell the staff what badges do cadets qualify for and how many to order.(I can build the last parts later if they get broken)

I'm not going to look at it now (beer o'clock in Belfast), but... can I (if I want) make changes to layout in one specific area. Ranges , like 32–44 (in your standards sheet) are a pain in the @ss. Extra formulaic steps are required to split the bit before the - from the bits after the -. It's a lot easier in two columns... one for "from" and one for "to"; or... somethimes just one, for "from". Can I split them if I feel i want to?

back on Sunday...

Absolutely! Feel free to break any of my layouts as I figured we may have to do that but I was unsure.

Thanks if you decide to help out, it is much appreciated!

Work in progress. I need YOU to check this over before going any further. It is (I think!!) done for M & F curl up. Test it. Is it correct?

I had to spend some time killing all your merged cells. Merged cells are the spawn of Satan, real 666 stuff. I sincerely hope that the man who invented them was lined up against a wall and shot.

They make formulae a TOTAL nightmare. It's impossible to cleanly select columns and rows. They destroy the logic of formulae... need I go on? See the www for helpful merged cell hints, written by one of the forum's wise men..

https://excel.solutions/2016/10/usin...ctively-excel/

So, I purged the lot from Fitness standards.

For Curl up:
=IF('Data and Result Entry'!$I5="","",IFERROR(LOOKUP('Data and Result Entry'!$I5,INDEX(IF(N4="M",'Fitness Standards'!$F$4:$I$10,'Fitness Standards'!$F$27:$I$33),MATCH('Data and Result Entry'!$D5,'Fitness Standards'!$A$4:$A$10,0),),1+COLUMN(INDIRECT("1:4"))),1))

copied down. Let me know and if we're good to go, I'll carry on...

Wow! thanks a bunch; it looks great, but when I am looking closer it there are now just a few errors on the page of standards for each badge, but I can easily fix that after to be the correct numbers, especially since they are all just the lowest number in the range.
Your merged cells link made me chuckle!

I have attached the pdf with the official result (curl up for age 13 or 14 on the female standard page under the silver column), but I believe it is just a transcription error.

I love the formula, and it is exquisite in comparison to what I came up with. (file attached to show mine)

It is unfortunately broken based on this training year, it will be perfect for a normal training year, but due to COVID, we are not assessing a shuttle run test (so it is not counted) as well as we are not removing the lowest result as long as it is not the shuttle run.
Normally, we remove the data based on the lowest grade achieved, and if it is the shuttle run, then that is their result. If it is one of the muscular tests, we can ignore the result and go one level up.

Yes, I see the issue. That one is now fixed.

I was getting very frustrated with the empty columns and the merged cells and maybe didn't take as much care as I should have transcribing the numbers. I'll give them a quick once-over, but you'll have to check them yourself.

As far as I can see I still have 5 columns to fix up (push up, L back saver, R back saver & 20 m shuttle. I'll give this another 15 mins tonight. If I haven't completed it, I'll do so early tomorrow morning (Irish time).

If there is anything more that I need to twiddle with, let me know before my morning time (it's now 17:40, Sunday here).

Here it is... Over to you. see comments in previous post.. and this one:

Sit & reach male... there is no difference between the stretch needed and B, S, G & E. The formula as formulated (does that make sense??) gives any male who gets 20 cm, 5 points. Correct? if not, what?

I will definitely go through the values as I would myself had made errors!

For sit & reach, it is a pass or fail, which means you are correct that there are only 5 points or 1 because they attempted.

I can likely take it from here, I will play around, and I'm sure I can figure out making an edit if there is something slightly off.


Overall it looks amazing, and thank you very much for all of the help; I would have been stuck for many more days before I got anywhere near this level; nesting formulas more than one level is new to me!

If you run into problems... just shout. Thanks to Covid and a back problem I'm here too often right now!!

Thanks for the rep.