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 03:20 AM.
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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
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?
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)))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks