Solved how to make an what if situation

January 9, 2015 at 11:22:24
Specs: Windows 7
I am trying to get excel to populate certain cells if they dont meet my conditions example

=IF(B59,>=100%)--(B58*B60)*(10%),IF(B59, <100%),(B59*0)
what am I doing wrong?


See More: how to make an what if situation

Report •

✔ Best Answer
January 9, 2015 at 13:01:51
Excel is not able to do an "in between" calculation in one step. It has to perform 2 separate operations, joined by a Logical operator:

In English:

If C59>75% AND C59<=100%, then...

In Excel syntax:

IF(AND(C59>75%,C59<=100%),C59*C27,"")

All arguments within the AND() function must be TRUE for the AND to be TRUE.

With an OR() any single argument can be TRUE for the OR to be TRUE

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 9, 2015 at 11:34:17
The syntax of your IF function is incorrect. You have an extra comma and some extra parentheses. In addition, I don't know what the -- is for.

The syntax should be:

=IF(logical_test, value_if_true, value_if_false)

logical_test: B59>=100% (no comma)

value_if_true: B58*B60*10% (no need for all those parentheses)

value_if_false: B59*0 [or just 0] (no need to logically test B59 again. Also, B59*0 = 0)

=IF(B59>=100%,B58*B60*10%,0)

BTW...if you don't want to place a "0" in your cell you can use this, which will return an "empty cell" as the value_if_false:

=IF(B59>=100%,B58*B60*10%,"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
January 9, 2015 at 11:38:22
Awesome thank you I will try it out

Report •

#3
January 9, 2015 at 11:51:25
ok Now im getting a #value space when i try to put in =IF(AND(B59)=FALSE,B59>100%<75%,""),IF(AND(B59)=FALSE,B59<100%>75%,B59*B27)
when I try to get a dollar value ie B59 is a % and B27 is a $ amt

message edited by Srribs


Report •

Related Solutions

#4
January 9, 2015 at 13:01:51
✔ Best Answer
Excel is not able to do an "in between" calculation in one step. It has to perform 2 separate operations, joined by a Logical operator:

In English:

If C59>75% AND C59<=100%, then...

In Excel syntax:

IF(AND(C59>75%,C59<=100%),C59*C27,"")

All arguments within the AND() function must be TRUE for the AND to be TRUE.

With an OR() any single argument can be TRUE for the OR to be TRUE

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 9, 2015 at 13:06:41
OMG you rock that is what i needed to know!!!!! <3<3<3

Report •

#6
January 9, 2015 at 13:08:56
I think you need to do a little research into the syntax you are using for your functions.

Don't take this the wrong way, but it looks as if you are making stuff up as you go along and hoping it will work. I see no logic in the construction of your formulas.

Each function has a very specific syntax and the placement of each comma and parenthesis is extremely important. The syntax of each function can be found in the Excel Help files as well as on many Excel related websites. A simple Google search will produce a ton of information on the use and syntax of just about any Excel function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 9, 2015 at 13:14:50
I am very new to Excel other than basic functions so this is a great learning experiance and I really appreciate all of your help.

Report •

#8
January 9, 2015 at 16:37:43
Good luck and come on back whenever you have a question.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question