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?

Report •


#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.

MIKE

http://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
Read this How-To

http://www.computing.net/howtos/sho...

Then post a sample of your data from your spread sheet that corresponds to the formula.

Your formula uses columns A, H and I

I don't understand how your last post relates to your formula.

MIKE

http://www.skeptic.com/


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
The reason I asked you to post from your spreadsheet, after reading the indicated How To, is so I don't have to spend ten minutes copying your data and then having to transposing it all before I can even begin to try and figure out what your problem is.
If you read the How To, and then copy and paste an example of your spreadsheet, it saves me the time of trying to interpret what you mean and figure out exactly what goes where.
I figured what most of your data means, but what and where does that very last line of your post go?
That would be the line that reads Start Date.
In Cell D1 or someplace else?

I didn’t want to enter that many columns and the rest of the useless information so I just entered what was necessary.
But you did not read the How To.
If you need to post your data then create a test sheet with only the data that you need and post that.

Your not a newbie here, you should know how to post your data by now.

MIKE

http://www.skeptic.com/


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 1


Or 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.

MIKE

http://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 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

Sorry 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?

MIKE

http://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?????????

MIKE

http://www.skeptic.com/


Report •

#17
November 27, 2010 at 21:13:04
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


Sorry 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.

MIKE

http://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

MIKE

http://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 = 15

MIKE

http://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 10th

In row three above
the DATE, 8/17/1997 minus the DATE 8/24/1997 equal -7 days because the 24th is GREATER than the 17th

The first part of your formula is: =IF( A5 - $I$25 > H21

What values do you have in A5
What values do you have in I25
What values do you have in H21

MIKE

http://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

http://www.skeptic.com/


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?

MIKE

http://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
Review what I wrote in reply #10 it should give you an idea of what you need to do.

Also, try again to post an example of your data, if your going to be posting here, you should know how to do this.

Step 1. Click the Pre icon found above the Reply box.
Step 2. Enter your data/table(s) or VBA code between the tags.
Step 3. Click Preview Follow Up below on the right, DO NOT press Submit Follow Up, to see if you like the way it looks.
Step 4. If you need to fix the layout, the top window shows what your messages looks like, the bottom window allows you to fix and modify your message. Make your changes.
Step 5. At the top there is a box with "Check To Show Confirmation Page Again" check this box.
Step 6. Click either Confirm button to Preview the post again.
You can now edit your message again, make your changes/edits and to review these new edits start at step 5.
You can keep making corrections until you get it the way you want.


If your going to be doing DATE math, I would advise you to review the =DATEDIF() function, it might make your life easier or confuse you even more.

MIKE

http://www.skeptic.com/


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 this
A
B
C

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 correctly

MIKE

http://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 )

MIKE

http://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??

MIKE

http://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/97
A1 = 8/10/97

I6 - A1 equals 7 days

Is seven days, greater than the contents of I1 ?

If true, then show the contents of H1
If false, then show zero

That 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?

MIKE

http://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 right

Got 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.

MIKE

http://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 Weeks
Column 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.

MIKE

http://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?

MIKE

http://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))))

MIKE

http://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))))

MIKE

http://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
What answer do you get?
What answer are you supposed to get?

MIKE

http://www.skeptic.com/


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,20
What 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?

MIKE

http://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 Used
Contents of Cells
Formulas

MIKE

http://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.

MIKE

http://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.

MIKE

http://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?

MIKE

http://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, etc

Try it and see if it works.

MIKE

http://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

MIKE

http://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))))

MIKE

http://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 0
this makes your first week actually the numbers 0 through 7
this makes your first week actually the numbers 0 through 6


Date math is not easy and can get confusing.

MIKE

http://www.skeptic.com/


Report •


Ask Question