+ Reply to Thread
Results 1 to 11 of 11

output a result based on 3 variables

  1. #1
    Registered User
    Join Date
    01-09-2021
    Location
    Dundas, ON Canada
    MS-Off Ver
    MS Office 365
    Posts
    6

    output a result based on 3 variables

    I am looking to build an excel document where it outputs a result from multiple different variables and is looking for some help in the variable's layout. I have boxed myself into a corner and cannot figure out a formula to do what I am trying to do.

    The final goal is that it runs automatically by the inputs given on the initial sheet.


    Sheets
    - Data and Result Entry
    - Auto Badge Calc
    - Fitness Standards

    Data and Result Entry
    This sheet is just where the staff members fill out data to make the grade sheet work.

    Auto Badge Calc
    This sheet allows the data to be turned in to a Badge (table on the right-hand side to show what number and colour respond to what badge and how many to order.

    Fitness Standards
    This sheet holds the variables, which are Age, Gender, Result (which yes, there are multiple Different tests to all have separate details)




    The issue is I need to find a result that outputs in the range of 0-5, or I can do 2 step formulas, which is fine, but I cannot get past how to look up in a database on 3 variables being the age, gender, and result.
    (the reason for the 2 sets of min formulas under the two separate result tabs is regardless of how well a person does on the main testing, if they get a low grade on the shuttle run, they only get the lowest result. Long term, I would love to ignore one of the results for the main test as you can normally excuse one variable as long as the rest of the result still has the minimum number. some way of min formula with the taking 2nd lowest but the ni think if you had 2 of the lowest umber it would break)

    Any help on this would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,116

    Re: 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!!
    Glenn



  3. #3
    Registered User
    Join Date
    01-09-2021
    Location
    Dundas, ON Canada
    MS-Off Ver
    MS Office 365
    Posts
    6

    Re: output a result based on 3 variables

    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)
    Last edited by Scubadiver735; 01-09-2021 at 01:58 PM.
    Adam Seidlitz

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,116

    Re: output a result based on 3 variables

    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...

  5. #5
    Registered User
    Join Date
    01-09-2021
    Location
    Dundas, ON Canada
    MS-Off Ver
    MS Office 365
    Posts
    6

    Re: output a result based on 3 variables

    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!
    Last edited by Scubadiver735; 01-09-2021 at 10:37 PM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,116

    Re: output a result based on 3 variables

    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...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-09-2021
    Location
    Dundas, ON Canada
    MS-Off Ver
    MS Office 365
    Posts
    6

    Re: output a result based on 3 variables

    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.
    Last edited by Scubadiver735; 01-10-2021 at 11:24 AM. Reason: Spelling/Grammar

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,116

    Re: output a result based on 3 variables

    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).

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,116

    Re: output a result based on 3 variables

    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?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-09-2021
    Location
    Dundas, ON Canada
    MS-Off Ver
    MS Office 365
    Posts
    6

    Re: output a result based on 3 variables

    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!

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,116

    Re: output a result based on 3 variables

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 11-15-2020, 08:50 AM
  2. Formula to Output Result Based on Consecutive Cells Containing X
    By apk120490 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-29-2019, 12:58 PM
  3. Replies: 1
    Last Post: 02-02-2015, 11:31 AM
  4. Search cell text and output different values based on result in another cell
    By tbarn1980 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2013, 09:54 PM
  5. [SOLVED] finding an output from 5 variables in an array using exterior input user variables
    By Allsort in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-09-2013, 11:16 AM
  6. [SOLVED] Need Help with Formula for Eligibility Result Form based on 2 input Variables
    By jlepp06 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2013, 08:08 AM
  7. Modify IF Output based on Result
    By floricita in forum Excel General
    Replies: 6
    Last Post: 01-12-2011, 11:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1