Share via

Using Excel built in functions with MIN and IF with multiple conditions

Mikeska, Jack 1 Reputation point
2021-02-23T19:18:22.473+00:00

So I'm trying to figure out if I need to use the MIN function and IF function separately or together or an entirely different function for my problem.

I'm trying to find the minimum value in several ranges (i.e. I3:I54,J3:J54,K3:K54,L3:L54,M3:M54) but if within those ranges the cells are blank or essentially 0 in value I want the value I have in N17:R17 to be put in cells N21:R21 respectively. If those ranges do have values I want it to evaluate the minimum value and record that in N21:R21.

Example:

Take range I3:I54 and the minimum value is blank or 0 essentially but in cell N17 the value is 410,000. I want the value from N17 to be recorded in N21 since the range evaluated to 0.

If the range J3:J54 evaluates the minimum value to be 210,000 then I want that value to be recorded in O21 as 210,000 since it was not 0.

I hope this makes sense. I tried using IF and MIN functions in all sorts of ways separately and combined and it doesn't work the way I want it to. Should I be using those functions to perform these operations I want or separate ones entirely? I've searched online and have tried for hours to figure this out and can't. HELP!!!!!!!!!!!

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz._ 8,991 Reputation points
    2021-02-24T12:06:16.787+00:00

    Hi @Mikeska, Jack

    Example with I3:M21:

    71557-demo.png

    In N21 and copy right until R21:
    =IF(SUM(--(I3:I21=0)), N17, MIN(I3:I21)) if your run Excel 365
    =IF(SUMPRODUCT(--(I3:I21=0)), N17, MIN(I3:I21)) if you run another version or need backward compatibility

    1 person found this answer helpful.
    0 comments
  2. Emily Hua-MSFT 27,601 Reputation points
    2021-02-26T10:29:40.817+00:00

    @Mikeska, Jack

    If you want to use Excel formula, please refer to Lz-3068's reply.

    Or you could also check this formula =IF(OR(I$3:I$22=0,I$3:I$22=""),N$17,MIN(I$3:I$22)).
    72437-capture51.png

    But if your want to use Graph API, which is related to development code, and this is out of my support scope.
    Thanks for your understanding.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.