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

Salary Surveys
Property & Casualty, Life, Health & Pension

Health Actuary Jobs
Insurance & Consulting jobs for Students, Associates & Fellows

Actuarial Recruitment
Visit DW Simpson's website for more info.
www.dwsimpson.com/about

Casualty Jobs
Property & Casualty jobs for Students, Associates & Fellows


Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 02-05-2019, 05:33 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 11,450
Default

yall are lazy

put this in C1
=IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8=B1))=1,FA LSE,TRUE)
drag down, adjust ranges if needed
__________________
Pronouns: yore/you're
Reply With Quote
  #12  
Old 02-05-2019, 05:52 PM
llcooljabe's Avatar
llcooljabe llcooljabe is offline
Member
CAS
 
Join Date: Aug 2002
Posts: 23,271
Default

Quote:
Originally Posted by mathmajor View Post
yall are lazy

put this in C1
=IF(SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8=B1))=1,FA LSE,TRUE)
drag down, adjust ranges if needed
won't a countifs work just as well but easier to understand?

Countifs($a$1:$a$8,a1,$b$1:$b$8,1), and then filter on the 1's.

alternatively take your formula out of the if, and also filter on the 1's
__________________
www.GoodNewsNow.info
Propoganda
Reply With Quote
  #13  
Old 02-06-2019, 11:12 AM
Cavaliere Cavaliere is offline
Member
SOA
 
Join Date: May 2016
Posts: 304
Default

Quote:
Originally Posted by phpBB View Post
C1: =A1&B1
D1: =(COUNTIF($A:$A,A1)-COUNTIF($C:$C,C1)) > 0
This seems to be working so far. Thank you!
Reply With Quote
  #14  
Old 02-06-2019, 12:25 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 11,450
Default

Quote:
Originally Posted by phpBB View Post
Did you verify that your solution works before calling other people lazy?
yes, mom

I thought s/he wanted a one-formula method.

and I was calling the remove duplicates/sort thing lazy lol
__________________
Pronouns: yore/you're
Reply With Quote
  #15  
Old 02-06-2019, 12:32 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is offline
Member
SOA
 
Join Date: Jul 2014
Posts: 29,211
Default

Quote:
Originally Posted by mathmajor View Post
yes, mom

I thought s/he wanted a one-formula method.

and I was calling the remove duplicates/sort thing lazy lol
I don't know if it's lazy but I don't think it's a good idea to go on with partial information with advice like

Step 1) Destroy the data
__________________
the president replied, "I don't care. I believe Putin."
Reply With Quote
  #16  
Old 02-06-2019, 01:49 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 82
Default

Use an array. If the minimum B column value for a particular A column value is different to the maximum, then you have more than one distinct value. Copy this formula down column C (using Ctl + Shift + Enter, when you enter it in C1) -
{=IF(MIN(IF($A$1:$A$8=A1,$B$1:$B$8))<>MAX(IF($A$1: $A$8=A1,$B$1:$B$8)),1,0)}
Reply With Quote
  #17  
Old 02-06-2019, 03:06 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 82
Default

Quote:
Originally Posted by phpBB View Post
Is your formula correct? I get all 0's with that.
Yes, you're right - it doesn't work with alphas. If you convert the alphas to their ascii code you can do it -

{=IF(MIN(IF($A$1:$A$8=A1,CODE($B$1:$B$8)))<>MAX(IF ($A$1:$A$8=A1,CODE($B$1:$B$8))),1,0)}
Reply With Quote
  #18  
Old 02-06-2019, 04:51 PM
Colymbosathon ecplecticos's Avatar
Colymbosathon ecplecticos Colymbosathon ecplecticos is offline
Member
 
Join Date: Dec 2003
Posts: 6,068
Default

Quote:
Originally Posted by Knoath View Post
Yes, you're right - it doesn't work with alphas. If you convert the alphas to their ascii code you can do it -

{=IF(MIN(IF($A$1:$A$8=A1,CODE($B$1:$B$8)))<>MAX(IF ($A$1:$A$8=A1,CODE($B$1:$B$8))),1,0)}
This assumes that column B entries are single characters.
__________________
"What do you mean I don't have the prerequisites for this class? I've failed it twice before!"


"I think that probably clarifies things pretty good by itself."

"I understand health care now especially very well."
Reply With Quote
  #19  
Old 02-06-2019, 04:56 PM
yoyo's Avatar
yoyo yoyo is offline
Member
CAS
 
Join Date: Dec 2001
Posts: 23,221
Default

NERD ALERT!
Reply With Quote
  #20  
Old 02-06-2019, 05:57 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 82
Default

Quote:
Originally Posted by Colymbosathon ecplecticos View Post
This assumes that column B entries are single characters.
True. This would be much easier using SQL where you can use max or min on alphanumerics.
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 10:06 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.21776 seconds with 11 queries