Actuarial Outpost Random Excel Question
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Salary Surveys Property & Casualty, Life, Health & Pension Health Actuary JobsInsurance & Consulting jobs for Students, Associates & Fellows Actuarial Recruitment Visit DW Simpson's website for more info. www.dwsimpson.com/about Casualty JobsProperty & Casualty jobs for Students, Associates & Fellows

#1
04-17-2019, 11:18 AM
 vividox Lead Guitarist Non-Actuary Join Date: Dec 2008 Favorite beer: Avery Mephistopheles Posts: 57,556
Random Excel Question

I'm trying to do a SUMIFS. In column AO I have a formula that returns a value if it can find it and "" otherwise. The SUMIFS is then conditioned on AO:AO being "<>". The result is pulling everything, not just the non-blanks.

As a test, I filled down the formula AP2=AO2<>"" in column AP; results were as expected - all the blanks are false and all the non-blanks are true.

I have another column that is nearly identical (but it's populated with values, not formulas) and if I use X:X is "<>" instead of AO:AO is "<>" I get the desired result. In addition to that, if I use AO:AO is "" I get the expected result.

So, can I not use AO:AO is "<>" as a condition if AO:AO is populated with formulas? I feel like I'm missing something obvious here.
__________________
This post was crafted using a special blend of herbs and sarcasm.

Last edited by vividox; 04-17-2019 at 11:23 AM..
#2
04-17-2019, 11:34 AM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 96,521

Whether AO:AO has formulas or not is not the problem. It is whatever the result of that formula is.
I'm not sure where "" is on the < or > scale of everything else. Sometimes they are ignored.
__________________
"Facebook is a toilet." -- LWTwJO

"45 es un titere" -- Seal of The President of The United States of America protest art
#3
04-17-2019, 11:48 AM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 8,338 Blog Entries: 1

Yeah, that's weird. Got the same result. I guess it's doing (or not doing) some kind of implicit conversion.
__________________
L’humour est la politesse du désespoir
#4
04-17-2019, 11:52 AM
 vividox Lead Guitarist Non-Actuary Join Date: Dec 2008 Favorite beer: Avery Mephistopheles Posts: 57,556

Yeah, apparently there is some kind of conversion happening at some point in the process. I was able to get it to work by using a wildcard instead of a logical operator:

=SUMIFS(..., AO:AO, "?*")
__________________
This post was crafted using a special blend of herbs and sarcasm.
#5
04-17-2019, 11:54 AM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 8,338 Blog Entries: 1

__________________
L’humour est la politesse du désespoir