CODEWITHSUNDEEP
excelif-statement
Hassan
Hassan

Reputation: 13

Excel query with regards to MAX/IF statements

I have what I hope is a simple, doable question, but that being said, I haven't been able to figure it out.

How can I alter the following code such that if F5 were 0, that the cell would display 0? In all other circumstances, it should display a minimum of '43'.

The latter part of the code works; i.e: the minimum that is displayed is 43, however, I don't know how to alter it so that if F5 were 0, it would display 0.

=MAX(SUM(IF(F5<=0,0,39),
IF(F5>30,(C5*0.08),
IF(F5>20,(C5*0.07),
IF(F5>10,(C5*0.06),
IF(F5>5,(C5*0.05),
IF(F5>2,(C5*0.04),
IF(F5>1,(C5*0.03),
IF(F5>=0.25,(C5*0.02),
IF(F5>=0,(0.03*C5*F5),0))))))))),43)

Thanks a lot!

Upvotes: 0

Views: 67

Answers (3)

n8.
n8.

Reputation: 1738

Two thoughts: wherever you have F5<=0 it should probably be F5<0, since you want F5=0 to be a particular thing. Also, this is very hard to look at in a cell since it's so many levels nested. You might consider a UDF:

Function getNum(F5 as Single, C5 as Single) as Single

  'Start with initial value of 43
  getNum = 43

  'Change return value to zero if F5 is zero
  If F5 = 0 Then
    getNum = 0
  'Otherwise add some more, depending on inputs
  ElseIf F5 < 0 Then
    getNum = getNum + 39
  ElseIf F5 > 30 Then
    getNum = getNum + C5*0.08
  ElseIf F5 > 20 Then
    getNum = getNum + C5*0.07
  ElseIf F5 > 10 Then
    getNum = getNum + C5*0.06
  ElseIf F5 > 5 Then
    getNum = getNum + C5*0.05
  ElseIf F5 > 2 Then
    getNum = getNum + C5*0.04
  ElseIf F5 > 1 Then
    getNum = getNum + C5*0.03
  ElseIf F5 > 0.25 Then
    getNum = getNum + C5*0.02
  ElseIf F5 > 0 Then
    getNum = getNum + C5*F5*0.03
  End If

End Function

Put that in a module and then just call it in the cell with =getNum(F5, C5)

I haven't tested it so it might not be exactly right, but you can see how much easier that is to understand than a bunch of nested IFs.

Upvotes: 0

SanjaySir
SanjaySir

Reputation: 1

=MAX(SUM(IF(F5<=0,0,39),
IF(F5>30,(C5*0.08),
IF(F5>20,(C5*0.07),
IF(F5>10,(C5*0.06),
IF(F5>5,(C5*0.05),
IF(F5>2,(C5*0.04),
IF(F5>1,(C5*0.03),
IF(F5>=0.25,(C5*0.02),
IF(F5>0,(0.03*C5*F5),
IF(F5=0,0,0)))))))))),43)

Upvotes: 0

Jop.pop
Jop.pop

Reputation: 345

General If condition in Excel is 

=IF(F5=0,0,43)

So, I guess you will get the required answer if you start with

=IF(F5=0,0,<ur calculated formula>)

I am not sure about the working of your formula

Upvotes: 1

Related Questions