**BleepingComputer**, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having

**no ads**shown anywhere on the site.

**Click here to Register a free account now!**or read our

**Welcome Guide**to learn how to use this site.

# MS OFFICE 2007 EXCEL "IF" FUNCTION

### #1

Posted 18 February 2011 - 09:32 AM

SHEET:

Uploaded with ImageShack.us

PROBLEM:

IF A2+90 IS MORE THAN 1500 THEN B2=1

IF A2+90+90 IS MORE THAN 1500 THEN C2=1 BUT IF B2=1 THEN C2=0

IF A2+90+90+90 IS MORE THAN 1500 THEN D2=1 BUT IF C2=1 THEN D2=0

IF A2+90+90+90+90 IS MORE THAN 1500 THEN E2=1 BUT IF D2=1 THEN E2=0

....AND SO ON

PLEASE HELP!

### BC AdBot (Login to Remove)

### #2

Posted 18 February 2011 - 01:05 PM

Something like

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

http://www.techonthenet.com/excel/formulas/if_nested.php has a good example at the bottom.

### #3

Posted 19 February 2011 - 12:41 AM

1. You're using month names: the nested "IF" statement only allows 7 levels, so don't try it with 12 months.

2. I hope those constants 90 and 1500 will never (ever, ever) change, because if they may, it might be better to enter them as variables (perhaps on a new hidden and/or protected Row 20) and refer to them instead of typing them into each cell function, like this:

Suppose cell A20 = 90, and B20 = 1500. Now one could enter the same condition into each cell copied down, and change them later, from B2 reading something like this:

=IF(INT(($A2+$A$20)/$B$20)>0,1,0)

Mind you, that will generate more 1's than you want. You want to suppress the later 1's, so that requires a different function.....UNLESS you calculate an additional condition for the IF statement that makes sure this is the first match in this row. Perhaps you'll need a series of index multipliers could be included in, say, Row 21, like this:

Column A B C D E etc.

0 1 2 3 4

Because then you can use them to test in each row for earlier 1's, with an entry in cell B2 like this:

1. Large enough? 2. FIRST TIME?

=IF(AND(INT($A2+(B$21*$A$20)/$B$20=1),[CONDITION 2]),1,0)

But I'd use a space " " or null"" instead of the zeros to make your display more easily read, too.

### #4

Posted 19 February 2011 - 08:55 AM

I want only for 5 months, not more than 5 months

**Edited by hamluis, 24 March 2011 - 10:10 AM.**

Removed unnecessary quote.

### #5

Posted 19 February 2011 - 09:22 AM

**Edited by hamluis, 24 March 2011 - 10:11 AM.**

Removed unnecessary quote.

### #7

Posted 04 March 2011 - 06:33 PM

There are lots of possible ways to do that. Instead of the nested IFs, I've suggested an "index" row that should mean less work, let's suppose that can be Row 21, reading across:

A21=0, B21=1, C21=2,D21=3,E21=4, F21=5.

Then your second condition could ask: Would the previous cell in the row qualify too? Because if it does, this is not the first 1 in the row.

So the function in cell B2 could read:

=IF(AND(INT(($A2+(B$21*$A$20))/$B$20)>0,INT(($A2+((B$21-1)*$A$20))/$B$20)<1),1,0)

That looks cumbersome, but you only have to write it once (in B2), then copy across, then down without change. There'll be easier ways, I'm sure, but that's the richness of Excel functions, and there is a mountain of detail and examples in Excel's Help files.

That last zero could instead be a "" set, giving a tidier display, too.

Do hope this solves your problem: and starts you on another learning project.

### #8

Posted 25 March 2011 - 08:02 AM

=IF(A2=1,0,IF($A2+90*(col()-1)>1500,1,0)

then you can drag the formula in B2 across to F2, and you can all these formulas down to rows 3,4,5 etc.

There is a bit of trickery here, which is that we really want this formula for C2 onwards, checking the cell to the left every time, but we can get away with using the same thing in B2, because if A2 did happen to contain a 1 then we know that 1+90 < 1500, so a result of 0 is fine.

Using col() gives the number of the column where the formula appears, so for F2 for example, this is 6, and 6-1 is 5 so we get 90*5, which seems to be what you wanted.

Hope this helps

*John O'Farrell*

**Professional geek, consultant and trainer.**

MCT, MCSA, MMI, MBMSS, CWNA, COS (I like to have lettuce after my name)

My personal blog - Getting IT right

### #9

Posted 26 March 2011 - 07:41 PM

"Trickery" like this and the professionals who delivered it kept the industry alive when memory cost more than 1,000,000 times what it does now.

Keep learning.

### #10

Posted 27 March 2011 - 08:32 PM

(1) incomplete designPROBLEM:

IF A2+90 IS MORE THAN 1500 THEN B2=1

IF A2+90+90 IS MORE THAN 1500 THEN C2=1 BUT IF B2=1 THEN C2=0

IF A2+90+90+90 IS MORE THAN 1500 THEN D2=1 BUT IF C2=1 THEN D2=0

IF A2+90+90+90+90 IS MORE THAN 1500 THEN E2=1 BUT IF D2=1 THEN E2=0

for line 1: what about C2, D2, E2, F2 ?

for line 2: what about result for D2, E2, F2 for every row if propagate formula down?

for line 3: what about result for E2, F2 for every row if propagate formula down?

for line 4: what about result for F2 for every row if propagate formula down?

(2) what would you expect for these amounts?

I doubt this list would be the wanted results (though who knows??)

amount march apr may june july

200 0 0 0 0 0

1300 0 0 1 0 1

1200 0 0 0 1 0

900 0 0 0 0 0

435 0 0 0 0 0

and few more multiples of 90 added below

1590 1 0 1 0 1

1680 1 0 1 0 1

1770 1 0 1 0 1

1860 1 0 1 0 1

1950 1 0 1 0 1

I suspect that once a "1" occurs, other cells in the row should have a zero or be blank, but we don't know.

(3) Inserting few rows with constants and precalculated values wouldn't hurt either (I did it on row 13)

V1 1500 v2 90

col# 1 2 3 4 5

COLx90 90 180 270 360 450

with data beginning in in A18 (header in A17), then the formula to propagate right and down would read: =IF(A18=1,0,IF($A18+B$15>$B$13,1,0))

(4) FYI: in excel from office2003 it's COLUMN() not COL()

Having learning fun, and just my 2bits

**Edited:**this looks terrible here, but if you copy into excel it should all fall into correct cells.

**Edited by tos226, 27 March 2011 - 08:36 PM.**

### #11

Posted 28 March 2011 - 10:27 PM

What insufficient design? Get B2 right, then copy that cell across as far as F2, then copy B2:F2 down as far as you like. That works for me with my formula (5 March) and does as well for AdamV's (25 March). Did you read the previous traffic?

Expectations are really interesting: My formula offers what I

**thought**5thForce wanted:

200 0 0 0 0 0

1300 0 0 1 0 0

1200 0 0 0 1 0

900 0 0 0 0 0

435 0 0 0 0 0

1400 0 1 0 0 0

but on closer study, I found AdamV delivered accurately what he actually specified, though that looks a bit strange:

200 0 0 0 0 0

1300 0 0 1 0 1

1200 0 0 0 1 0

900 0 0 0 0 0

435 0 0 0 0 0

1400 0 1 0 1 0

So 5thForce got two answers for the price of one. And I'll let AdamV, a BC Adviser, defend his suggested formula against your alternative.

FYI noted, and again that's really for AdamV.

### #12

Posted 29 March 2011 - 09:53 AM

1. I only threw in my 2bits about making the formula (Adam's in this instance) diferently. Heck, the purpose is to save a microsecond per cell The results I posted match Adam's.

2. I suspect that what 5thForce wants is, as I wrote above, and as your formula shows, that once a "1" occurs, cells to the right should be zero. So that's what I mean by "incomplete" design. We weren't told what to do in all cells to the right.

Are we having fun yet?

### #13

Posted 29 March 2011 - 01:19 PM

v2 90

col# 1 2 3 4 5

90 180 270 360 450

amt march apr may june july

200 0 0 0 0 0

1300 0 0 1 0 0

1200 0 0 0 1 0

900 0 0 0 0 0

435 0 0 0 0 0

1090 0 0 0 0 1

1200 0 0 0 1 0

1300 0 0 1 0 0

1410 0 1 0 0 0

1411 1 0 0 0 0

1500 1 0 0 0 0

**IF**we want results with "1" occuring once per row, then this formula in B6 propagated right, then B7:F7 propagated down, seems to do the job:

=IF(AND($A7+B$4>$B$1,$A7+A$4<=$B$1),1,0)

or =IF(AND($A7+B$4>$B$1,$A7+A$4<=$B$1),1,"") if prefer blanks to zeros.

I added few values just for checking.

Paste it all at A1.

Make sure A3 is blank - it's essential for looking back (alternative is some circular reference).

Row 5 is blank, row 6 is table headings.

### #14

Posted 29 March 2011 - 02:57 PM

I hope 5thFoprce stops back by to test it

### #15

Posted 29 March 2011 - 07:23 PM

@

**Eyesee**

Re:"In the beginning there was the command line."

and in the end is everything hidden for your own good ? ? !

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users