+ Reply to Thread
Results 1 to 3 of 3

Displaying two possible IF function outcomes in one cell

Hybrid View

  1. #1
    Registered User
    Join Date
    02-22-2016
    Location
    Miami,Florida
    MS-Off Ver
    2013
    Posts
    1

    Displaying two possible IF function outcomes in one cell

    Hey guys!
    I'm a personal trainer, and I'm working on a training spreadsheet for a few of my clients to serve as a template.
    What I have right now is an IF statement that calculates the load to be used based on a cells defined name.
    The problem I'm encountering is that I have two possible percentages of the 1RM (one rep max) in several cells, thus resulting in a #VALUE error.

    Formula is as follows:
    =IF(D9="SQUAT",MROUND(SQMAX*I9,5),IF(D9="BENCH",MROUND(BNMAX*I9,5),IF(D9="DEADLIFT",MROUND(DLMAX*I9,5),)))
    It works if the percentage is fixed (70%,75%,80%,etc)
    I want it two display two possible loads to be used if the percentage is, for example, 77.5-80%

    Is this possible?
    Attached Files Attached Files
    Last edited by BoazR; 02-23-2016 at 03:43 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Displaying two possible IF function outcomes in one cell

    at first glance:
    I8: 72.5%
    I9: 77.5%-80%
    J8:
    Formula: copy to clipboard
    =IFERROR(IF(C8="SQUAT",MROUND(SQMAX*MID(I8,1,SEARCH("-",I8)-1),5),IF(C8="BENCH",MROUND(BNMAX*MID(I8,1,SEARCH("-",I8)-1),5),IF(C8="DEADLIFT",MROUND(DLMAX*MID(I8,1,SEARCH("-",I8)-1),5),)))&"-"&IF(C8="SQUAT",MROUND(SQMAX*MID(I8,SEARCH("-",I8)+1,999),5),IF(C8="BENCH",MROUND(BNMAX*MID(I8,SEARCH("-",I8)+1,999),5),IF(C8="DEADLIFT",MROUND(DLMAX*MID(I8,SEARCH("-",I8)+1,999),5),))),IF(C8="SQUAT",MROUND(SQMAX*I8,5),IF(C8="BENCH",MROUND(BNMAX*I8,5),IF(C8="DEADLIFT",MROUND(DLMAX*I8,5),))))
    result:
    J8: 85
    J9: 90-95


    Edit: fixed
    Last edited by sandy666; 02-23-2016 at 04:21 AM.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,953

    Re: Displaying two possible IF function outcomes in one cell

    =IF(LEN(I8)-LEN(SUBSTITUTE(I8,"-",""))=0,MROUND(CHOOSE(MATCH(C8,{"Squat","Bench","Deadlift"},0),SQMAX,BNMAX,DLMAX)*I8,5),MROUND(CHOOSE(MATCH(C8,{"Squat","Bench","Deadlift"},0),SQMAX,BNMAX,DLMAX)*LEFT(I8,FIND("-",I8)-1)/100,5)&" - "&MROUND(CHOOSE(MATCH(C8,{"Squat","Bench","Deadlift"},0),SQMAX,BNMAX,DLMAX)*MID(I8,FIND("-",I8)+1,LEN(I8)),5))
    Ben Van Johnson

+ 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. Macro for displaying all possible outcomes by changing 12 input variable
    By varunsinghbais in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2014, 03:48 AM
  2. IF function - two outcomes
    By Leahsco in forum Excel General
    Replies: 4
    Last Post: 08-06-2014, 11:29 AM
  3. If function with Three Possible Outcomes
    By cpalmer72 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-20-2013, 10:49 PM
  4. [SOLVED] An IF function with 4 outcomes
    By Lija in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2013, 04:34 AM
  5. [SOLVED] If function with 3 outcomes. As well as 2nd IF function to factor positive or negitive
    By Danimal615 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2013, 02:36 PM
  6. Help with IF function for 3 outcomes
    By randr in forum Excel General
    Replies: 3
    Last Post: 06-09-2012, 05:48 PM
  7. Displaying multiple outcomes
    By crossroader35 in forum Excel General
    Replies: 2
    Last Post: 08-21-2005, 05:05 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