Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Question about If Statment in Excel

Posted on 2014-10-25
Medium Priority
158 Views
Last Modified: 2014-10-26
Is it possible to have an if statement in excel that will do the following and if so how would you write it?
I have column B and Column M.

I want the if statement to go down column B.
I want the if statement to do if possible to see if column M2 is 0 if it is to do the following in B2.
Copy B1 formula into B2 and if M2 is not 0 to use another formula.

When I say copy B1 formula into B2, I mean do not automatic change the column letters or numbers, others words if it days B1 = G1 + H1 I want it to stay B1 = G1 + H1 not B2 = G2 + H2
Because I want the outcome that was in B1 to be in B2. If this possible I will be taking the formula from B1 to B90

I hope this is clear enough.
0
Question by:starview
4 Comments

LVL 23

Accepted Solution

Ejgil Hedegaard earned 1200 total points
ID: 40404148
Use \$ to lock the cell references when copying.
In B2: =IF(M2=0,\$B\$1,"another formula")
0

LVL 15

Assisted Solution

Haris Djulic earned 200 total points
ID: 40404265
If i understood correctly, if M(any row) is equal to 0 then copy the value from one row above, then this formula will work:

``````=IF(M2=0;INDIRECT("B"&ROW()-1);"another formula")
``````

but if you always want to show the value from B1 then the Ejgil formula will do the trick..
0

Author Comment

ID: 40404381
I am trying to use Ejgil formula
=IF(F15=0,\$B\$14,"=((16*B14)-((G15-F15)/(3500/16)))/16")
But when I put an amount into F15 it show wrong data. it looks like it is formatting that cell
I have to copy a blank cell back to F15
0

LVL 27

Assisted Solution

Glenn Ray earned 600 total points
ID: 40404462
starview,

You're now referencing a difference column altogether (column F, cell F15) and you're on a different row (15 vs. 2).

You just need to remove the quotes in the [value_if_false] argument:
=IF(F15=0,\$B\$14,((16*B14)-((G15-F15)/(3500/16)))/16)

Regards,
-Glenn
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month13 days, 3 hours left to enroll

#### 971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.