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 online now
Lead Guitarist
Non-Actuary
 
Join Date: Dec 2008
Favorite beer: Avery Mephistopheles
Posts: 57,503
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: 95,427
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
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: 7,738
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.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
Reply With Quote
  #4  
Old 04-17-2019, 11:52 AM
vividox's Avatar
vividox vividox is online now
Lead Guitarist
Non-Actuary
 
Join Date: Dec 2008
Favorite beer: Avery Mephistopheles
Posts: 57,503
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: 7,738
Blog Entries: 1
Default

Ha. Might want to leave a comment on that one.
You can link this thread.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
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 04:30 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.17614 seconds with 11 queries