Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 5
There are 1 users currently browsing forums.
|
 |
07-03-2009, 08:37 PM
|
|
Registered User
|
|
Join Date: 09 Jun 2009
Location: NSW, Australia
MS Office Version:Excel 2003
Posts: 47
|
|
|
AutoSum Strange behaviour
Please Register to Remove these Ads
Hi
this is really bizarre, and i'm not sure what is going on.
but I have a formula that is in ColF Row4, and it is copied down to ColF Row 13. But trouble is that when I autosum this column I get 6, but the answer should be 8.
The data in Rows A-E are extracted from an external data source.
any ideas.
I've attached the spreadsheet.
=IF((AND(A4=7,D4="")),1,(IF((OR(A4=7,D4>=2)),D4,0)))
Last edited by ssdsibes; 07-04-2009 at 02:20 AM.
|
07-03-2009, 10:20 PM
|
 |
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,532
|
|
|
Re: AutoSum Strange behaviour
The value in D4 is TEXT. It looks like a 2, but it's a text string.
Just type another 2 back over it and your sum works.
__________________
If you've been given good help, use the  icon to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
07-03-2009, 10:40 PM
|
|
Registered User
|
|
Join Date: 09 Jun 2009
Location: NSW, Australia
MS Office Version:Excel 2003
Posts: 47
|
|
|
Re: AutoSum Strange behaviour
Yes, so it does! but when i refresh my data, the data in D is coming from an external data source that is refreshed each time the spreadsheet is open, so i'm back to square one. and it's possible for more data to come into ColD in the other rows.
is it possible to format the data in msquery before coming into excel?
|
07-03-2009, 10:53 PM
|
|
Forum Guru
|
|
Join Date: 21 Mar 2008
Location: Bakersfield, CA
MS Office Version:2003 (can read 2007 files)
Posts: 9,532
|
|
|
Re: AutoSum Strange behaviour
Only you know what's going on with your data import. But if it's coming in as text, it is probably simpler just to trick the text back into a numeric...put this in F4 and copy down:
=IF((AND(A4 = 7, D4 = "")), 1,(IF((OR(A4 = 7, D4 >= 2)), D4 * 1, 0)))
__________________
If you've been given good help, use the icon to give reputation feedback, it is appreciated.
Always put your code between [code] and [/code] tags.
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
|
07-04-2009, 02:20 AM
|
|
Registered User
|
|
Join Date: 09 Jun 2009
Location: NSW, Australia
MS Office Version:Excel 2003
Posts: 47
|
|
|
Re: AutoSum Strange behaviour
I worked out that the number was coming from an alpha-numeric field, so i tested with another numeric field, and the auto-sum worked fine.
But I did try your formula and yes it worked beautifully!
Thanks for your help.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
