Ecel Formula Problem

Closed
Report
-
 warwickswinger -
Hello,
I have this formula that I need to edit. When e14 is 27.6 or 30 then use 864, all other values to use 950. Can anyone help me with this. Thanks In advance
=((E13*22.75)*(E14/IF(EXACT(E14,30),864,950)*2)*B16*(B12/1000))

5 replies


=if(OR($E$14=27.6,$E$14=30),864,950)

Should do the trick. It works in the following way:

if(criteria, output when TRUE, output when FALSE) is the formula you're applying.

Your criteria is "Is E14 equal to either 27.6 or 30?". The simplest way to do this is to use the OR function.

OR will output 'true' if any of its criteria are met, and 'false' if none of them are met.

So,

OR(E14=27.6, E14=30) will output 'TRUE' if E14 is equal to either 27.6 or 30, and 'FALSE' in all other instances.

Your value if TRUE (when E14=27.6 or when E14=30) is 864
Your value if FALSE (when E14<>27.6 or E14<>30) is 950



You can omit the $ symbol in my suggestion if you wish. Leaving the $ symbol in place means that the formulae will still refer to E14 if you copy and paste it elsewhere (absolute cell reference). Removing the $ symbol means that as you copy and paste, the reference to E14 moves as well (relative cell reference).

By the way, I forgot to mention in my first post that the function EXACT is the incorrect function to use.

EXACT has two arguments:

EXACT(a,b)

where a and b are text strings.

Its usage is to compare two text strings and return a logical output depending whether or not they are identical.

As you are comparing two numercial arguments, EXACT isn't the one to use.
Hmm I tried the way you suggested in both and still get errors. I am fairly new to this level of excell
=((E13*22.75)*(IF(OR(E14=27.6,E14=30),864,950)*2)*B16*(B12/1000))

But I get a much higher calulation that it should be

The example you give and the question asked in the first post aren't quite the same thing though :)

The formula I gave answers the question "How do I choose an output of 864 or 950 depending on the value contained in e14?".



I will try and break down what your formula is doing, then perhaps that might help clarify where it's not giving you the expected results?

Your formula "=((E13*22.75)*(IF(OR(E14=27.6,E14=30),864,950)*2)*B16*(B12/1­000))"


Is doing the following:

Take 864 or 950 (depending on the value in E14) and multiply it by 2.

The result of this is then multiplied by e13 and then 22.75.

The result of this is then multiplied by b16.

The result of this is then multiplied by b12, and then the whole lot is divided by 1000.




Without the spreadsheet in front of me, it's quite tricky to tell precisely what you are trying to achieve.

Perhaps it might help if you email me a copy of the spreadsheet and an outline of what you're trying to do, and I'll try and fix it for you?

My email address is warwickswinger at hotmail dot com.