Articles

Solved if formula in excel

July 13, 2011 at 08:09:23
Specs: Windows XP

pls help,

can somebody write me a simple formula (for me is complicated) in excel.
what I need is this:
I have D14 sheet in which I enter lenght.
in H19 sheet I need formula which will do this:
if lenght is up 25 then enter 200 in H19sheet
if lenght is beetwen 25 and 50,or equal 25 than enter 300
and if lenght is bigger than 50 or equal 50 than enter 400 in that H19 sheet.

tnx :)


See More: if formula in excel

Report •


#1
July 13, 2011 at 08:25:51
✔ Best Answer

Try this:

=IF(AND(D14>0,D14<=25),200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,"")))

MIKE

http://www.skeptic.com/


Report •

#2
July 13, 2011 at 08:35:43

thx for reply, but it won't work :(

Report •

#3
July 13, 2011 at 08:38:54

but it won't work

Not a lot of info here.

It worked for me on my test sheet.

Placing the formula in Cell H19 does not work?

Do you get any kind of error message?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 13, 2011 at 08:48:42

when I copy that in cell h19.
the answer is: the formula you enter containes an error.I tryed several times.
I don't know what else to look,what I did wrong?

Report •

#5
July 13, 2011 at 09:09:36

Did you make any type of change to the formula?

Make sure you copied the whole formula.

There should be three (3) parentheses marks at the end.

MIKE

http://www.skeptic.com/


Report •

#6
July 13, 2011 at 09:21:16

no,I just copied formula and enter it in cell.
It's still the same.
tnx for patience.
still won't work.
always the same.

Report •

#7
July 13, 2011 at 09:45:15

Post the formula you are using.

Does the error message offer to correct the error?

MIKE

http://www.skeptic.com/


Report •

#8
July 13, 2011 at 09:59:08

=IF(AND(D14>0,D14<=25),200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,"")))

when I press enter excel highlight this 0,D14 in the first part of formula.


Report •

#9
July 13, 2011 at 10:31:15

What version of Excel are you using?

Work fine on my machine.

Try this:

=IF(AND(D14>"",D14<=25),200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,"")))

EDIT ADDED:

excel highlight this 0,D14 in the first part of formula.

Make sure it is a zero not the letter O.

MIKE

http://www.skeptic.com/


Report •

#10
July 13, 2011 at 10:43:00

I use excel 2007.
still the same.it was number 0 not letter O.
I think I will do this manualy.
I'm shame to torture you any more time.
thank you very much for your help and time you spent on me :)

Report •

#11
July 13, 2011 at 11:11:25

Sorry, don't know what's going on.

I'm using 2007 and all the formulas work fine on my machine.

The section where it shows the error is a simple =AND() function:

AND(D14>0,D14<=25)

It needs the comma between the statments: D14>0 and D14<=25

MIKE

http://www.skeptic.com/


Report •

#12
July 13, 2011 at 11:22:02

probably I'm doing something wrong.I just don't know what.

Report •

#13
July 13, 2011 at 11:49:05

One more try:

Try this one:

=IF(ISBLANK(D14),"",IF(D14<=25,200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,""))))

Just copy and paste it into cell H19

With this formula, Zero is a number, so it will return 200 if you enter 0.

MIKE

http://www.skeptic.com/


Report •

#14
July 13, 2011 at 11:54:37

When I am using an IF statement to return values based on input that falls within various ranges of numbers, I prefer to do it backwards since Excel will stop evaluating the function as soon as it hits a TRUE condition.

It eliminates the need for the AND function and results in a shorter formula.

=IF(D14>=50,400,IF(D14>=25,300,200))

If you need the cell to be blank if a Zero exists, then just add an IF clause for that:

=IF(D14>0,IF(D14>=50,400,IF(D14>=25,300,200)),"")

If your lengths will always be whole numbers, then you don't need to check for >=, just use a value that is 1 less than the number you are trying to test for:

=IF(D14>0,IF(D14>49,400,IF(D14>24,300,200)),"")

Of course, there is also the VLOOKUP method, which allows the user to alter the table of values and never have to alter the formula if the criteria or desired results change.

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


Report •

#15
July 13, 2011 at 14:02:25

guys, I still get errors, but as they say ''video'' say 1000 words :)

http://www.youtube.com/watch?v=jRTl...

take a looksie if there is any willpower left in you.


Report •

#16
July 13, 2011 at 14:40:38

Heck, now that I've seen the video, the answer is easy:

"It's the strangest thing I've ever seen." ;-)

If I send you an email address via PM could you send me the workbook, with any confidential information removed/altered?

Check your PM messages.

DerbyDad03
Office Forum Moderator

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


Report •

#17
July 13, 2011 at 15:43:59

There is something about cell D14 that is not right.
As you paste in the formula, the first reference to D14 turns blue, the rest of the parens appear ok also.

Strange.


MIKE

http://www.skeptic.com/


Report •

#18
July 13, 2011 at 22:03:07

:))
thanks you both.
I'll sent it to you, of course.

Report •

#19
July 14, 2011 at 04:08:56

I received the sheet.

Why doesn't it look like the one in the video?

I was able to paste the formulas into H15 and put values in D14 with no problem, but I don't think I'm working on the same workbook as in the video.

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


Report •

#20
July 14, 2011 at 04:16:07

because I have some on home comp and some on job.
same problem was in each.
can you send me sheet back?

Report •

#21
July 14, 2011 at 04:36:00

I can sent that one only late in the evening, when I came home.

Report •

#22
July 14, 2011 at 05:49:30

I can send it back later this evening as it is on my home computer.

I'll leave the formulas in the workbook.

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


Report •

#23
July 14, 2011 at 06:01:44

can't wait. thank you for trouble.


Report •

#24
July 14, 2011 at 09:22:30

Did you actually try the formulas in the one you sent me after you deleted the formulas that you mentioned in your email?

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


Report •

#25
July 14, 2011 at 11:01:14

yes, 10 times. and on other comp and few minutes ago my friend came and he tried.
everything is the same in each sheet on every comp we have.

Report •

#26
July 14, 2011 at 12:38:00

Have you tried typing in the formulas as opposed to cut and paste?

Before you do that, try this:

Paste in this formula and after you get the error, which should highlight ,"", manually delete and reenter the commas, one at a time.

=IF(ISBLANK(D14),"",IF(D14<=25,200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,""))))

If that doesn't help, try typing the formulas in by hand.

I'm looking for something related to how Excel is seeing the characters after the cut and paste.

Maybe the character codeing is getting screwed up.

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


Report •

#27
July 14, 2011 at 13:15:26

tried all that, manually enter formula. no change.

Report •

#28
July 14, 2011 at 14:33:15

I populated POPIS!F2:F151 with random numbers between 1 and 100.

I copied the same 2 formulas from this thread as you showed in your video and pasted them into H19 and I19 of the other 150 sheets.

That's 300 formulas in 150 sheets and not one single problem.

I don't know what else to say.

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


Report •

#29
July 14, 2011 at 14:55:26

You apparently have a corrupted sheet,
try saving the file as either a SYLK (Symbolic Link) file and/or HTML format,
close the file and reopen and re-save as a regular Excel file type .xls
see if that corrects the problem.

MIKE

http://www.skeptic.com/


Report •

#30
July 14, 2011 at 17:30:30

If he has a corrupted sheet, how come I had no problem entering the formulas in all 150 sheets without an error?

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


Report •

#31
July 14, 2011 at 18:11:52

DerbyDad03:

Quote from Microsoft, emphasis is mine:

"Saving the file in SYLK format is typically used to remove printer corruption. If you can open the corrupted Microsoft Excel file, you can "filter" it if you save it in SYLK format, close the file, and then reopen"

It may have been caused by his printer.

MIKE

http://www.skeptic.com/


Report •

#32
July 14, 2011 at 20:57:51

Mike,

Please provide a link to the quote from MS.

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


Report •

#33
July 15, 2011 at 06:12:24

unucica:

I read your emails this morning.

All I can think of is that, as Mike said, the original workbook is corrupted. Perhaps that by opening it on my machine, Excel "repaired" it and the repaired copy will work on any machine, even yours.

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


Report •

#34
July 15, 2011 at 06:38:06

But why is corrupted? what can I do to fix it in future?

Report •

#35
July 15, 2011 at 09:37:38

I can't answer either of those questions.

On thankfully rare occasions, an Excel spreadsheet will become corrupt and strange thing will happen. Sometimes the corruption can be narrowed down to a calle or range and fixed by deleting the range (not just the data) and rebuilding that part of the sheet.

Sometimes we never know where the problem is.

When you think about how complex the internal workings of Excel are, you can understand how a single misplaced bit could mess up the relationships between cells or sheets.

The only way to prevent losing a lot of work because of a corrupted file is to "save early, save often" and make backup copies of critical files.

Of course, where you store those back-up files is also very important. There's not much sense in saving the backups on the same system as the original.

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


Report •

#36
July 15, 2011 at 10:37:37

DerbyDad03

Please provide a link to the quote from MS.

http://office.microsoft.com/en-us/e...

MIKE

http://www.skeptic.com/


Report •

#37
July 18, 2011 at 10:24:38

understood. I'll try to be more careful in future.
thanks both for help.
if you are ever in Dubrovnik, Croatia...You have a drinks on me ( well...not literally on me :) )

Report •

#38
July 18, 2011 at 12:41:11

D*mn!

My son just got back from Dubrovnik less than a month ago!

He was there doing a study abroad semester at RIT's sister campus.

He had a fantastic time and I'm sure he would have had that drink for me.

He's gonna laugh when I tell him about this.

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


Report •

#39
July 18, 2011 at 13:20:53

what a small world! :))

better than with him since we are peers and maybe he has some extra trick in his sleeves ;-)



Report •


Ask Question