Welcome to 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.

MS OFFICE 2007 EXCEL "IF" FUNCTION

14 replies to this topic

#1 5thForce

5thForce

• Members
• 79 posts
• OFFLINE
•
• Gender:Male
• Location:india
• Local time:02:48 AM

Posted 18 February 2011 - 09:32 AM

PLEASE, Help Me To Get Right Formula For Excel Sheet

SHEET:

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

#2 Eyesee

Eyesee

Bleepin Teck Shop

• 3,545 posts
• OFFLINE
•
• Gender:Male
• Location:In the middle of Kansas
• Local time:04:18 PM

Posted 18 February 2011 - 01:05 PM

It could end up being fairly lengthy but try nested IF functions used with AND

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.
In the beginning there was the command line.

#3 Geoffc

Geoffc

• Members
• 92 posts
• OFFLINE
•
• Gender:Male
• Location:Warragul, Australia
• Local time:08:18 AM

Posted 19 February 2011 - 12:41 AM

In the spirit of learning, I'd suggest some more study for 5thForce.
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 5thForce

5thForce
• Topic Starter

• Members
• 79 posts
• OFFLINE
•
• Gender:Male
• Location:india
• Local time:02:48 AM

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 5thForce

5thForce
• Topic Starter

• Members
• 79 posts
• OFFLINE
•
• Gender:Male
• Location:india
• Local time:02:48 AM

Posted 19 February 2011 - 09:22 AM

SORRY! I DONT UNDERSTAND SECOND PART (OR SECOND FORMULA)

Edited by hamluis, 24 March 2011 - 10:11 AM.
Removed unnecessary quote.

#6 5thForce

5thForce
• Topic Starter

• Members
• 79 posts
• OFFLINE
•
• Gender:Male
• Location:india
• Local time:02:48 AM

Posted 21 February 2011 - 12:35 AM

#7 Geoffc

Geoffc

• Members
• 92 posts
• OFFLINE
•
• Gender:Male
• Location:Warragul, Australia
• Local time:08:18 AM

Posted 04 March 2011 - 06:33 PM

Sorry, 5thForce, had not realized I left you in trouble. That first function solves half your task: Is the score in this cell large enough to deserve a 1? But it doesn't meet the second need, Is this the first time 1 appears in the row? That will need a second condition in the "IF" statement.

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.

• Members
• 65 posts
• OFFLINE
•
• Gender:Male
• Location:Leeds, UK
• Local time:10:18 PM

Posted 25 March 2011 - 08:02 AM

In B2 you need:
=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
Due to global warming, eskimos now have more than 20 words for water 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 Geoffc

Geoffc

• Members
• 92 posts
• OFFLINE
•
• Gender:Male
• Location:Warragul, Australia
• Local time:08:18 AM

Posted 26 March 2011 - 07:41 PM

Super improvement, AdamV, expression half the size and removes the need for indexing suggested on line 21. I do hope 5thForce revisits to see the efficiency gain. My thanks at least.

"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 tos226

tos226

BleepIN--BleepOUT

• Members
• 1,588 posts
• OFFLINE
•
• Gender:Female
• Location:LocalHost
• Local time:04:18 PM

Posted 27 March 2011 - 08:32 PM

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

(1) incomplete design
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 Geoffc

Geoffc

• Members
• 92 posts
• OFFLINE
•
• Gender:Male
• Location:Warragul, Australia
• Local time:08:18 AM

Posted 28 March 2011 - 10:27 PM

Good contribution, tos226; can I feed your fire a little?

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 tos226

tos226

BleepIN--BleepOUT

• Members
• 1,588 posts
• OFFLINE
•
• Gender:Female
• Location:LocalHost
• Local time:04:18 PM

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 tos226

tos226

BleepIN--BleepOUT

• Members
• 1,588 posts
• OFFLINE
•
• Gender:Female
• Location:LocalHost
• Local time:04:18 PM

Posted 29 March 2011 - 01:19 PM

v1 1500
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 Eyesee

Eyesee

Bleepin Teck Shop

• 3,545 posts
• OFFLINE
•
• Gender:Male
• Location:In the middle of Kansas
• Local time:04:18 PM

Posted 29 March 2011 - 02:57 PM

You guys did good work with this!!

I hope 5thFoprce stops back by to test it
In the beginning there was the command line.

#15 tos226

tos226

BleepIN--BleepOUT

• Members
• 1,588 posts
• OFFLINE
•
• Gender:Female
• Location:LocalHost
• Local time:04:18 PM

Posted 29 March 2011 - 07:23 PM

Off-topic:

@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