Actuarial Outpost Excel: have raw numbers, in one cell can i see if any one is greater than 50% sum
 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

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

#1
12-27-2018, 10:23 AM
 Noonien Soong Member CAS Join Date: Sep 2018 Posts: 39
Excel: have raw numbers, in one cell can i see if any one is greater than 50% sum

Seasons greetings humans,

To make use case simple, say i have numbers in A1:C1. Is there a way to only use cell D1 to see if any of A1, B1, or C1 is greater than 50% of the total of sum A1:C1?

I'm trying to avoid making D1:F1 = A1/sum(A1:C1), B1/sum(A1:C1), and C1/sum(A1:C1), then having to make G1=if(MAX(D1:F1)>=.5,1,0).

Can this be done?

Cordially,

Noonien
#2
12-27-2018, 10:34 AM
 Dan Moore Member SOA AAA Join Date: Jan 2008 College: University of Dallas Posts: 2,307 Blog Entries: 1

Max(A1:C1)/Sum(A1:C1). If this expression > 0.5, you know the condition has been reached somewhere within A1:C1.
__________________
The best time to plant an oak tree is twenty years ago. The second best time is right now.
#3
12-27-2018, 10:38 AM
 SlowMotionWalter Member CAS Join Date: Jun 2013 Posts: 13,204

=IF(MAX(A1:C1)>SUM(A1:C1)/2,1,0)
__________________
Quote:
 Originally Posted by bigb You should learn to communicate in a serious manner before you expect a serious answer.
Spoiler:
Quote:
 Originally Posted by Kangaz wit Attitude Force of habit, 2pac's been typing "88" a LOT since Trump got elected.
Quote:
 Originally Posted by JMO You ought to see the bush. It's impressive.
Quote:
 Originally Posted by Pikachu 10:53 pm: some ppl dont take advices well
Quote:
 Originally Posted by mayo fan 9:45 pm: ao fan would be hot covered in mayo!
Quote:
 Originally Posted by Snikelfritz if you'd like I can come visit and dress up like a girl and get in some fights
Quote:
 Originally Posted by Kaner3339 i think everyone needs to do this type of thing to get a dose of reality and straighten people up. it's kinda like going to the mountains and becoming a monk except it's with hundreds of potatoes and a lot of stoners with tattoos in a kitchen
#4
12-27-2018, 10:49 AM
 Noonien Soong Member CAS Join Date: Sep 2018 Posts: 39

I see.
#5
12-27-2018, 05:34 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,278

=countif(a1:c1,">="&.5*sum(a1:c1))
__________________
Come on. Let's go space truckin'. Come on!
#6
12-27-2018, 05:36 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,278

=ROUND(MAX(A1:C1)/SUM(A1:C1),0)

i like this best
__________________
Come on. Let's go space truckin'. Come on!

Last edited by DeepPurple; 12-28-2018 at 02:12 PM..
#7
12-27-2018, 05:39 PM
 Patience Member SOA AAA Join Date: Sep 2001 Location: a kinder, gentler place Favorite beer: Scotch Posts: 48,701

Quote:
 Originally Posted by Noonien Soong Seasons greetings humans, To make use case simple, say i have numbers in A1:C1. Is there a way to only use cell D1 to see if any of A1, B1, or C1 is greater than 50% of the total of sum A1:C1? I'm trying to avoid making D1:F1 = A1/sum(A1:C1), B1/sum(A1:C1), and C1/sum(A1:C1), then having to make G1=if(MAX(D1:F1)>=.5,1,0). Can this be done? Cordially, Noonien
Yes
__________________
"I've been through the desert on a horse with no name...
In the desert you can remember your name
'Cause there ain't no one for to give you no pain"