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 :)
Try this: =IF(AND(D14>0,D14<=25),200,IF(AND(D14>25,D14<=50),300,IF(D14>50,400,"")))
thx for reply, but it won't work :(
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?
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?
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.
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.
Post the formula you are using. Does the error message offer to correct the error?
=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.
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.
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 :)
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
probably I'm doing something wrong.I just don't know what.
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.
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.
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.
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 ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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.
:))
thanks you both.
I'll sent it to you, of course.
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.
because I have some on home comp and some on job.
same problem was in each.
can you send me sheet back?
I can sent that one only late in the evening, when I came home.
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.
can't wait. thank you for trouble.
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.
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.
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.
tried all that, manually enter formula. no change.
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.
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.
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.
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, Please provide a link to the quote from MS.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
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.
But why is corrupted? what can I do to fix it in future?
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.
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 :) )
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.
what a small world! :)) better than with him since we are peers and maybe he has some extra trick in his sleeves ;-)
