# Whats wrong with this formula?

November 27, 2010 at 13:44:14
Specs: Windows Vista
 Ok so I am trying to calculate something regarding dates. Is there something wrong with this formula? The answer to the first cell is correct but when I copy it down it does not calculate it correctly. Im thinking if there is something wrong with it is that there are too many IF's. Thank you=IF(A5-\$I\$25>H21,I21,IF(A5-\$I\$25>H22,I22,IF(A5-\$I\$25>H23,I23,IF(A5-\$I\$25>H24,I24,0))))

See More: Whats wrong with this formula?

#1
November 27, 2010 at 15:09:58
 Read this How-To http://www.computing.net/howtos/sho...Then post a sample of your data that corresponds to the formula.If your dealing with DATES, then make sure they are all formatted as such, and not as TEXT.Your formula appears to be OK.MIKEhttp://www.skeptic.com/

Report •

#2
November 27, 2010 at 16:14:31
 You have to pay before the start date. If you pay on the first day and through the 1st week you get charged 5 if its 2 weeks its 10 and so on. If you pay before you dont ge charged. Thanks`A``B``C``D``8/10/97``Week 1``5``8/17/97``Week 2``10``8/24/97``Week 3``15``8/31/97``Week 4``20``9/7/97``9/14/97``Start date 8/16/97`

Report •

#3
November 27, 2010 at 16:18:07
 This one is better. You have to pay before the start date. If you pay on the first day and through the 1st week you get charged 5 if its 2 weeks its 10 and so on. If you pay before you dont ge charged. Thanks`A``B``C``8/10/97``Week 1``5``8/17/97``Week 2``10``8/24/97``Week 3``15``8/31/97``Week 4``20``9/7/97``9/14/97``Start date 8/16/97`

Report •

Related Solutions

#4
November 27, 2010 at 16:48:01

Report •

#5
November 27, 2010 at 17:53:39
 Columns B and C would be used as H and I. I didnt want to enter that many columns and the rest of the useless information so I just entered what was necessary. So columns B and C represent columns H and I.

Report •

#6
November 27, 2010 at 18:01:45
 re: "So columns B and C represent columns H and I."Come on, Mike! You couldn't figure that out? Sheesh.Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#7
November 27, 2010 at 18:09:44
 DerbyDad03 instead of sitting here and clowning on people who ask question maybe it would be easier not to do that instead of wasting your time on that and maybe even asnwer a question or two. Obviously I made a mistake and didn't mention that which was my fault. But you are very good at asking questions that have nothing to do with answering people posts. If you don't like me for some reason thats fine but why waste your time and post negative stuff and try and make people feel bad?

Report •

#8
November 27, 2010 at 19:38:22

Report •

#9
November 27, 2010 at 19:57:31
 I have read it I used the Pre tags but everytime I try to post something it comes out weird. It has only worked once correctly. Well honestly the start date can be wherever because we use that date to subtract. I put it in C6. I feel like everytime I write something its more confusing.

Report •

#10
November 27, 2010 at 20:15:56
 Is this what your data looks like?:``` A H I 5) 8/16/1997 21) Week 1 5 22) Week 2 10 23) Week 3 15 25) 8/10/1997 26) 8/17/1997 27) 8/24/1997 ```If it is, and your using the formula:=IF(A5-\$I\$25>H21,I21,IF(A5-\$I\$25>H22,I22,IF(A5-\$I\$25>H23,I23,IF(A5-\$I\$25>H24,I24,0))))Then your trying to compare a NUMBER with the TEXT string Week 1``` A5 = 8/16/1997 minus I25 = 8/10/1997 equals the NUMBER 6 ```that make your formula read: IF(A5-I25>WEEK 1Or does your data look like:``` A H I 5) 8/16/1997 21) 5 Week 1 22) 10 Week 2 23) 15 Week 3 25) 8/10/1997 26) 8/17/1997 27) 8/24/1997 ```Then modify the formula:=IF(\$A\$5-I25>\$H\$21,\$I\$21,IF(\$A\$5-I25>\$H\$22,\$I\$22,IF(\$A\$5-I25>\$H\$23,\$I\$23,IF(\$A\$5-I25>\$H\$24,\$I\$24,0))))An then the formula will return Week 1 for row 25,as Row 26 is only 1 day greater and row 27 is -8 days, they will both return zero.MIKEhttp://www.skeptic.com/

Report •

#11
November 27, 2010 at 20:20:19
 re: "..maybe even asnwer a question or two."OK, I'll give that a try.Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#12
November 27, 2010 at 20:42:21
 A H I8/10/97 Week 1 5 8/17/97 Week 2 108/24/97 Week 3 158/31/97 Week 4 209/7/979/14/97 Start Date 8/17/97Sorry I listed the start date incorrectly on the last one. This is how the chart should look. It doesn't look normal again it looks weird but this is the correct way. Sorry for the whole confusion

Report •

#13
November 27, 2010 at 20:45:12
 ``` A H I 8/10/97 Week 1 5 8/17/97 Week 2 10 8/24/97 Week 3 15 8/31/97 Week 4 20 9/7/97 9/14/97 Start Date 8/17/97```Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#14
November 27, 2010 at 20:52:46
 ``` A H I 8/10/97 Week 1 5 8/17/97 Week 2 10 8/24/97 Week 3 15 8/31/97 Week 4 20 9/7/97 9/14/97 Start Date 8/17/97 ```Ok, we have the column letters,now try for the Row numbers.When you post using the pre tags, replace the tabs with spaces.First copy you data into a text editor like Notepad or Jedit then search/replace all the tabs with spaces, the post using the pre tags.See if that makes it easier.Were either of my examples correct?MIKEhttp://www.skeptic.com/

Report •

#15
November 27, 2010 at 20:53:44
 Yes just like that. Thank you

Report •

#16
November 27, 2010 at 20:56:29
 Yes just like that.Like what?????????MIKEhttp://www.skeptic.com/

Report •

#17
November 27, 2010 at 21:13:04
 A H I 21 8/10/97 Week 1 522 8/17/97 Week 2 1023 8/24/97 Week 3 1524 8/31/97 Week 4 2025 9/7/9726 9/14/97 Start Date 8/17/97Sorry just like that was to DerbyDad but you posted before I replied. So no matter how I try to post it correctly it does not work. But this is how it should look. Sorry for driving you crazy.

Report •

#18
November 27, 2010 at 21:29:27
 Cali, your making this much harder then it has to be.Your formula is: =IF(A5-\$I\$25>H21,I21,IF(A5-\$I\$25>H22,I22,IF(A5-\$I\$25>H23,I23,IF(A5-\$I\$25>H24,I24,0))))you show as data``` A H I 21) 8/10/97 Week 1 5 22) 8/17/97 Week 2 10 23) 8/24/97 Week 3 15 24) 8/31/97 Week 4 20 25) 9/7/97 26) 9/14/97 Start Date 8/17/97 ```Where is A5?Is A5 now supposed to be I26?Again, were either of the two format examples given in reply #10 correct?Are you subtracting the date in I26 from the dates in column A?or are you subtracting the dates in column A from the date in I26?I believe your problem is which cells you anchor.MIKEhttp://www.skeptic.com/

Report •

#19
November 27, 2010 at 21:33:51
 I apologize. Yes I am subtracting the dates in columns A from I26. So the first one would be 0 I know that but when I copy that formula down it doesn't give me the correct answers.Yes it looks like it is correct.

Report •

#20
November 27, 2010 at 21:48:08
 I am subtracting the dates in columns A from I26.I26 = 8/17/1997``` A B C 1) 8/10/1997 8/17/1997 7 < formula B1-A1 2) 8/17/1997 8/17/1997 0 < formula B2-A2 3) 8/24/1997 8/17/1997 -7 < formula B3-A3 4) 8/31/1997 8/17/1997 -14 < formula B4-A4 5) 9/07/1997 8/17/1997 -21 < formula B5-A5 6) 9/14/1997 8/17/1997 -28 < formula B6-A6 ```Is this what your looking for?or this:``` A B C 1) 8/10/1997 8/17/1997 -7 < formula A1-B1 2) 8/17/1997 8/17/1997 0 < formula A2-B2 3) 8/24/1997 8/17/1997 7 < formula A3-B3 4) 8/31/1997 8/17/1997 14 < formula A4-B4 5) 9/07/1997 8/17/1997 21 < formula A5-B5 6) 9/14/1997 8/17/1997 28 < formula A6-B6 ```MIKEhttp://www.skeptic.com/

Report •

#21
November 27, 2010 at 21:51:50
 No, sorry I wanted to mention what I was looking for again but didn't want to make it confusing. What im looking for is if a fee is paid before the start date there is no charge, if it is paid on the 1st day and throughout the first week they would get charged 5 since it is 1 week late, if it is 2 weeks they would get charged 10, 3 weeks 15, 4 weeks or more 20.

Report •

#22
November 27, 2010 at 22:09:05
 So it should be If DATE = START DATE plus 1 - 7 days = 5 If DATE = START DATE plus 8 - 14 days = 10 If DATE = START DATE plus 15 - 21 days = 15MIKEhttp://www.skeptic.com/

Report •

#23
November 27, 2010 at 22:13:08
 Im not sure what that means it should be if the fee is paid before start date no charge. If it is paid on start date and till the 7 day then 5, 8-14=10, 15-21=15, so if that is what your saying then yes. But again date paid subtracted from start date. Thank you soooooo much I really appreciate your help.

Report •

#24
November 28, 2010 at 08:05:42
 Let's get back to basics.Your formula is testing for the difference between two DATES, ``` A B C 1) 8/10/1997 8/17/1997 7 < formula B1-A1 2) 8/17/1997 8/17/1997 0 < formula B2-A2 3) 8/24/1997 8/17/1997 -7 < formula B3-A3 4) 8/31/1997 8/17/1997 -14 < formula B4-A4 5) 9/07/1997 8/17/1997 -21 < formula B5-A5 6) 9/14/1997 8/17/1997 -28 < formula B6-A6 ```In row one above:the DATE, 8/17/1997 minus the DATE 8/10/1997 equal 7 days becuse the 17th is GREATER and the 10thIn row three abovethe DATE, 8/17/1997 minus the DATE 8/24/1997 equal -7 days because the 24th is GREATER than the 17thThe first part of your formula is: =IF( A5 - \$I\$25 > H21What values do you have in A5What values do you have in I25What values do you have in H21MIKEhttp://www.skeptic.com/

Report •

#25
November 28, 2010 at 10:38:59
 The value in A5 is 8/10/97 the value in I25 is the start date which is 8/17/97 the value in H21 it says week 1. And since it says week 1 they should get charged 5. Would it be easier if I gave you the answers?

Report •

#26
November 28, 2010 at 11:38:39
 So your formula looks like this:The first part of your formula is: =IF( A5 - \$I\$25 > H21```=IF(8/10/97 - 8/17/97 > week 1 DATE - DATE > TEXT ```You tell me what's wrong....MIKE

Report •

#27
November 28, 2010 at 11:58:31
 Numbers are mixed with text?

Report •

#28
November 28, 2010 at 12:02:15
 Numbers are mixed with text? Correct.When you subtract dates you get a number.So how do you correct your formula?MIKEhttp://www.skeptic.com/

Report •

#29
November 28, 2010 at 12:11:05
 I don't know maybe not use that part?

Report •

#30
November 28, 2010 at 12:30:22

Report •

#31
November 28, 2010 at 12:56:24
 In reply 10 you talk about modifying the formula. Ok so I use the Pre Icon button but it does not post correctly. I tried moving the Pre Icon button as well but regardless of what I do it does not post correctly. It comes out like thisABC

Report •

#32
November 28, 2010 at 13:20:05
 When you click on the pre button, two symbols should appear in your message,they should should be the word PRE surrounded by left & right arrows they should look like:< pre > < / pre >I've added spaces between everything to highlight what they look like, they will not have the spaces normally.Post your data between the two symbols:< pre >``` A B C 1) Data Goes here 2) more data here ```< / pre >This will keep the columns aligned.When you preview your message, you can now modify the columns so everything lines up correctlyMIKEhttp://www.skeptic.com/

Report •

#33
November 28, 2010 at 13:39:32
 Here you go. I got it``` A H I 8/10/97 Week 1 5 8/17/97 Week 2 10 8/24/97 Week 3 15 8/31/97 Week 4 20 9/7/97 9/14/97 Start Date 8/17/97 ```

Report •

#34
November 28, 2010 at 13:51:45
 Excellent..Now how do you fix your formula?First do a simple =IF() function to begin, it should be along the lines of:=IF( DATE - DATE > NUMBER, TEXT, 0 )MIKEhttp://www.skeptic.com/

Report •

#35
November 28, 2010 at 13:54:52
 =IF(A5-\$I\$25>I21,H21,0)?

Report •

#36
November 28, 2010 at 14:34:33
 ``` A H I 1) 8/10/97 Week 1 5 2) 8/17/97 Week 2 10 3) 8/24/97 Week 3 15 4) 8/31/97 Week 4 20 5) 9/7/97 6) 9/14/97 Start Date 8/17/97 ```Did you use the above data for your formula?? MIKEhttp://www.skeptic.com/

Report •

#37
November 28, 2010 at 15:00:42
 \$I\$6-A1>I1,H1,0? I really don't think that is correct but this is the best I can think of

Report •

#38
November 28, 2010 at 15:18:36
 So work out the results.I6 = 8/17/97A1 = 8/10/97I6 - A1 equals 7 daysIs seven days, greater than the contents of I1 ?If true, then show the contents of H1If false, then show zeroThat is how your formula:=IF(\$I\$6-A1>I1,H1,0)will work out.Now, what would be a simple =IF() formula to test for week 2?MIKEhttp://www.skeptic.com/

Report •

#39
November 28, 2010 at 15:21:19
 But I is not dates I is money so I dont think that is right

Report •

#40
November 28, 2010 at 15:34:25
 But I is not dates I is money so I dont think that is rightGot me confused now.Post a copy of your worksheet, with column letters and row numbers.Explain what each column represents, and what each row represents.Explain what your formula should do.Show what your expected results should look like.MIKEhttp://www.skeptic.com/

Report •

#41
November 28, 2010 at 15:50:15
 ```A H I J is for the results Results 1. 8/10/1997 Week 1 \$5 \$0 since they paid before the start date 2. 8/17/1997 Week 2 \$10 \$5 since its 1 week late 3. 8/24/1997 Week 3 \$15 \$10 since its 2 weeks late 4. 8/31/1997 Week 4 \$20 \$15 since its 3 weeks late 5. 9/7/1997 \$20 since its 4 weeks late 6. 9/14/1997 Start Date 8/17/1997 \$20 since its over 4 weeks late ```Column A is the date paid.Column B is WeeksColumn C is how much to charge per each week.So if someone paid before the start date they pay \$0 fees. If they paid on the 1st day and throughout the 1st week they would get charged \$5, week 2 \$10, week 3 \$15, week 4 and on \$20.

Report •

#42
November 28, 2010 at 15:59:36
 OK, I think I see what's going on:You can't compare NUMBERS & TEXT, and since column H is TEXT, we have to give a static number for the comparison,so your formula should be something like: =IF(A5-\$I\$25>"7",I21,=IF( DATE - DATE > NUMBER, TEXT, 0 )The number 7 is for Week 1, seven days.MIKEhttp://www.skeptic.com/

Report •

#43
November 28, 2010 at 16:05:15
 Thats not the whole formula though right?

Report •

#44
November 28, 2010 at 16:18:58
 Thats not the whole formula though right?You catch on quick. :-)So what is the rest of the formula?MIKEhttp://www.skeptic.com/

Report •

#45
November 28, 2010 at 16:24:45
 Im not sure if you making fun of me but is it another IF?

Report •

#46
November 28, 2010 at 16:41:11
 Im not sure if you making fun of me but is it another IF?I would never make fun of you, which leaves another IF, or just modify your original```=IF(A5-\$I\$25>"7",I21, You've got this far now what? IF(A5-\$I\$25>H22,I22, IF(A5-\$I\$25>H23,I23, IF(A5-\$I\$25>H24,I24,0)))) ```MIKEhttp://www.skeptic.com/

Report •

#47
November 28, 2010 at 16:57:45
 IF(A5-\$I\$25>14,I22, IF(A5-\$I\$25>21,I23, IF(A5-\$I\$25>28,I24,0))))????

Report •

#48
November 28, 2010 at 17:14:10
 Does it work? =IF(A5-\$I\$25>7,I21,IF(A5-\$I\$25>14,I22,IF(A5-\$I\$25>21,I23,IF(A5-\$I\$25>28,I24,0))))MIKEhttp://www.skeptic.com/

Report •

#49
November 28, 2010 at 17:20:15
 No it does not. It doesnt produce the answers im supposed to get

Report •

#50
November 28, 2010 at 17:25:42

Report •

#51
November 28, 2010 at 17:38:07
 I get 0,0,0,20,35659,0. I should be getting 0,5,10,15,20,20

Report •

#52
November 28, 2010 at 17:49:49
 I should be getting 0,5,10,15,20,20What formula are you using?What cells are you using?Post your spreadsheet.With column headings and row numbers, also what formulas your using in relation to what cells?MIKEhttp://www.skeptic.com/

Report •

#53
November 28, 2010 at 17:53:15
 Im using everything exactly from above the formulas and the spreadsheet

Report •

#54
November 28, 2010 at 18:01:28
 There are a number of examples above, which one?( I have three different configurations of your data....)We've done a number of formulas, which one?You have the spreadsheet in front of you, I can't see what's on it or what your doing.You have to post it so I can see it.Post everything.Column Headings,Row Numbers,Cells UsedContents of CellsFormulasMIKEhttp://www.skeptic.com/

Report •

#55
November 28, 2010 at 18:19:14
 Is there a way I can post the file on here?

Report •

#56
November 28, 2010 at 18:27:01
 No, only using the pre tags.``` A H I 5) 8/16/1997 21) Week 1 5 22) Week 2 10 23) Week 3 15 25) 8/10/1997 26) 8/17/1997 27) 8/24/1997 ```Is this what your data looks like?Note the row numbers and column letters.MIKEhttp://www.skeptic.com/

Report •

#57
November 28, 2010 at 18:56:52
 Ok I think I made a mistake when posting. It is exactly like this``` A H I 5) 8/10/97 6) 8/17/97 7) 8/24/97 8) 8/31/97 9) 9/7/97 10) 9/14/97 20) Week 21) 1 \$5 22) 2 \$10 23) 3 \$15 24) 4 \$20 25) Start date 8/17/97 ```I hope this helps

Report •

#58
November 28, 2010 at 19:21:41
 In your formula you forgot to anchor the cells containing the amounts:=IF(A5-\$I\$25>7,\$I\$21,IF(A5-\$I\$25>14,\$I\$22,IF(A5-\$I\$25>21,\$I\$23,IF(A5-\$I\$25>28,\$I\$24,0))))There are TWO additional things you have to do to get this to work,One: what happens if the date subtraction equals exactly 7?Your formula uses just the GREATER than symbol.MIKEhttp://www.skeptic.com/

Report •

#59
November 28, 2010 at 19:30:00
 Is it >=?

Report •

#60
November 28, 2010 at 19:40:27
 Correct.So now your formula looks like:=IF(A5-\$I\$25>=7,\$I\$21,IF(A5-\$I\$25>=14,\$I\$22,IF(A5-\$I\$25>=21,\$I\$23,IF(A5-\$I\$25>=28,\$I\$24,0))))The last problem is a bit tricky, and you are not the first to be stumped by it.An =IF() function stops after it finds a true statement, it does not continue down the formula to the end.So if your calculation of dates comes out to 14, you first test returns true, because 14 is greater than 7.But, this is NOT what your looking for, you need it to check further down the formula.Can you figure out how to correct this final problem?MIKEhttp://www.skeptic.com/

Report •

#61
November 28, 2010 at 20:01:42
 No I really don't know what else is missing

Report •

#62
November 28, 2010 at 20:09:00
 OK, I'll give you this one.You need to reverse the sequence of your test.Test the higher numbers first.So your formula should be:=IF(A5-\$I\$25>=28,\$I\$24,IF(A5-\$I\$25>=21,\$I\$23,IF(A5-\$I\$25>=14,\$I\$22,IF(A5-\$I\$25>=7,\$I\$21,0))))Notice we check for 28 first, then 21, etcTry it and see if it works.MIKEhttp://www.skeptic.com/

Report •

#63
November 28, 2010 at 20:19:44
 I see what you did but it stil does not give me the correct results.

Report •

#64
November 28, 2010 at 20:32:08
 It works for me.``` A B H I 5) 8/10/97 0 <=IF(A5-\$I\$25>=28,\$I\$24,IF(A5-\$I\$25>=21,\$I\$23,IF(A5-\$I\$25>=14,\$I\$22,IF(A5-\$I\$25>=7,\$I\$21,0)))) 6) 8/17/97 0 < 7) 8/24/97 5 < 8) 8/31/97 10 < 9) 9/7/97 15 < 10) 9/14/97 20 <=IF(A10-\$I\$25>=28,\$I\$24,IF(A10-\$I\$25>=21,\$I\$23,IF(A10-\$I\$25>=14,\$I\$22,IF(A10-\$I\$25>=7,\$I\$21,0)))) 20) Week 21) 1 \$5 22) 2 \$10 23) 3 \$15 24) 4 \$20 25) Start date 8/17/97 ```MIKEhttp://www.skeptic.com/

Report •

#65
November 28, 2010 at 20:46:01
 The results should be 0,5,10,15,20,20 if it is paid on the same day as the start date it still counts as week 1

Report •

#66
November 28, 2010 at 20:57:33
 It's how your measuring the time between dates.=IF(A5-\$I\$25>=21,\$I\$24,IF(A5-\$I\$25>=14,\$I\$23,IF(A5-\$I\$25>=7,\$I\$22,IF(A5-\$I\$25>=0,\$I\$21,0))))MIKEhttp://www.skeptic.com/

Report •

#67
November 28, 2010 at 21:15:56
 I dont get it? This gives me the correct answers. how did you do that?

Report •

#68
November 30, 2010 at 13:26:39
 I dont get it?If you pay on the first day and through the 1st week you get charged 5 If you pay the first day, which is 8/17/97, then this is the same as your start date, Since 8/17/97 minus your Start Date equals 0this makes your first week actually the numbers 0 through 7this makes your first week actually the numbers 0 through 6Date math is not easy and can get confusing.MIKE

Report •