Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Fill in a brief DW Simpson Registration Form
to be contacted when new jobs meet your criteria.


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 04-17-2019, 11:18 AM
vividox's Avatar
vividox vividox is offline
Lead Guitarist
Non-Actuary
 
Join Date: Dec 2008
Favorite beer: Avery Mephistopheles
Posts: 57,561
Default 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..
Reply With Quote
  #2  
Old 04-17-2019, 11:34 AM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 96,755
Default

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.
How about returning 0 instead of ""?
__________________
"Facebook is a toilet." -- LWTwJO

"45 es un titere" -- Seal of The President of The United States of America protest art
Reply With Quote
  #3  
Old 04-17-2019, 11:48 AM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is online now
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 8,499
Blog Entries: 1
Default

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
Reply With Quote
  #4  
Old 04-17-2019, 11:52 AM
vividox's Avatar
vividox vividox is offline
Lead Guitarist
Non-Actuary
 
Join Date: Dec 2008
Favorite beer: Avery Mephistopheles
Posts: 57,561
Default

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.
Reply With Quote
  #5  
Old 04-17-2019, 11:54 AM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is online now
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 8,499
Blog Entries: 1
Default

Ha. Might want to leave a comment on that one.
You can link this thread.
__________________
L’humour est la politesse du désespoir
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 06:35 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.14930 seconds with 11 queries