Need help with a formula

[Luke_Thom1]

I need a formula that takes volumes and calculates the cost based on volume requirements and discount per separate cells:

This is the formula that I have been working off of, not working though 

=IFOR(C35>=1200,1200*E35,C35<1200,C35*E35)

Another:

=IF(C35<1200>=3600,C35-1200)*F35

Problem with the above is that any volumes not within the range displays as a negative amount, need it to return a number zero - 0 

[#2]

@Luke_Thom1 

Please have a look at the syntax of IF(). It can't work the way you write it.

IFOR() does not exist in Excel. But you can use OR() within an IF query to query several conditions with IF.

=IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2)

[#3]

@Luke_Thom1 

The first one might be

=MIN(C35, 1200)*E35

The second one is confusing. Perhaps

=MAX(C35-1200, 0)*E3

or

=MAX(MIN(C35, 3600)-1200, 0)*E35

[#4]

@Luke_Thom1 

The formulas provided by @Hans Vogelaar  might be best for your purposes.

But the following might help you understand the syntax of IF expressions.

Your first formula was almost right.  Write:

=IF(C35>=1200, 1200*E35, IF(C35<1200, C35*E35) )

or simply:

=IF(C35>=1200, 1200*E35, C35*E35)

If C35>=1200 is false, we know that C35<1200 is true.  So IF(C35<1200... is superfluous.

-----

Your second formula and description are ambiguous.  You wrote that if C35 is ``not within the range``, the formula should ``return a number zero``.

First, what range?  You wrote C35<1200>=3600.  We might interpret that to mean C35<1200 or C35>=3600 (!).  But presumably you mean 1200<C35<=3600.  That means 1200<C35 and C35<=3600.

However, we cannot write 1200<C35<=3600 in Excel.  Instead, we might write:

=IF(AND(1200<C35, C35<=3600), C35-1200)*F35

or

=IF(AND(1200<C35, C35<=3600), (C35-1200)*F35, 0)

Technically, the last form is more efficient, albeit more "verbose" by a just few characters.

Second, do you really intend to return zero for C35>3600?!  That is what you wrote (``not within the range``).

Or do you mean:

1. If C35<=1200, return zero

2. Else if C35<=3600, return (C36-1200)*F35

3. Else C35>3600, so return (3600-1200)*F35, not zero

That is what the formula provided by @Hans Vogelaar  implements.

Or we might write:

=IF(C35<=1200, 0, (MIN(3600, C35) - 1200)*F35)