Use IF statement in Excel with set of cells

Copper Contributor

Hi,

I need to use the if statement with set of cells, say C4:C33, and the return for TRUE condition is the same as cell value, while for false condition the return value is zero. The following is an explanation:

 

=MIN(IF(C4:C33<C40,C4:C33,0))

 

However, this form is giving invalid value because I am using the same set as a True result.

Anyone can give an idea how to do this please?

 

Thanks

14 Replies

@m81hassan_51 

Maybe this?

=MINIFS(C4:C33,C4:C33,"<"&C40)

 

Perhaps, this is what you need:
=(MIN(C4:C$33)<C$40)*C4

Thanks for your reply, What I exactly want, is that, if the value of specifc cell in column C is lower than the fixed value (placed in cell C40) the the output from If-statement is the same value of specific C-cell, else, the output value is zero. C40 is only used for condition/comparesion and not used as an output neither in True nor in false conditions.

 

Thanks

 

You need no MIN. Thus, what you need is:
=(C4<C$40)*C4
The above formula returns either C4 or 0.
What I need is to use a set of cells (C4:C22) instead of only one cell C4.

The use of min function is to serve other purpose. The overall picture is that, I have a set of numbers which represent a test scores between 0 and 50. What I want to find is the highest score below the succeed point which is 25. For example, If I have the scores: 10,20,23,30,40,49; the value I need to find should be 23 in this example.

Here, C40=Constant=25. Thanks
Your new logic is now entirely different from the old one. Can please provide a clear example of your data and desired result?

Ok,

Lets us assume we have the following students scores:

 

Student No.       Score

-------------       ----------

 1                           41

2                            20

3                            23

4                            27

5                            30

6                            10

7                            49

 

the test maximum possible score is 50. That is the minimum success degree that student need to take for passing the exam is 25.

I need to find thclosest student score to 25 for failed students (below 25).

In this above example, the required score will be 23 for student no. 3...

What I am doing is that storing the 23 in a specific cell, say cell C40. and the students score are along column C.

then, follow anyone of the following algorithms:

 

1) find the minimum difference between C40-value and the student score which is bellow C40

2) find the maximum value of student score which is still bellow C40.

 

Thanks a lot for your help

 

 

Please further expand your assumptions by filling in the results you want in Column D.

Hi Twifoo: There is no column D, the following is what exactly happen:

 

Student No. Score

------------- ----------

C1 =                  41

C2 =                 20

C3 =                  23

C4 =                  27

C5  =                30

C6 =                 10

C7 =                 49

.

.

.

C40 = 25

 

C41 = closest score to C40 value and lower than C40 value.

 This means, C41 cell have the formula I want to use to fulfill the required task.

In summary, there is a set of scores and I want to find the closest score to C40 value and lower than C40 value using one formula and store this value is cell C41.

 

Thanks

 

@m81hassan_51 

It seems the desired result is a secret.

One last guess:

=MAXIFS(C4:C33,C4:C33,"<"&C40)

 

I am getting the following error with this gess: #NAME? ..
This means that there is no such command in Excel.
May you please double check the tool name?
Similar to Detlef’s formula, but backward-compatible:
=MAX(INDEX((C4:C33<C40)*C4:C33,0))
This one is working, but it gives a wrong answer. Let me experiment with this formula, I think I will get something useful from it.

Thank you very much Twifoo
C4:33 refers to the range of scores. You may have to modify that range if it is different from the actual range where the scores are stored. Good luck!