Actuarial Outpost treat for excel geeks
 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

 Berlin - Madrid - Rome - Paris - Hamburg - Warsaw Barcelona - Vienna - Milan - Munich - Prague - Cologne Actuarial Jobs in Europe Athens - Amsterdam - Frankfurt - Copenhagen Hannover - Dublin - Brussels - Lyon - Zurich

#1
05-04-2012, 10:27 AM
 Smart Actuary Member Join Date: Sep 2011 Posts: 7,795
treat for excel geeks

solve my problem and make your day:

i have 3 rows of numbers.

if all numbers in a column have same sign, i want to flag it 1, if 2 have same sign flag 2.

write me a formula to do that.

__________________
I've always had a problem with the average macho man; they've always been a threat to me - Kurt Cobain
#2
05-04-2012, 10:32 AM
 vividox Lead Guitarist Join Date: Dec 2008 Favorite beer: Saison-Brett Posts: 44,235

=IF(OR(AND(A1>0,A2>0,A3>0),AND(A1<0,A2<0,A3<0)),1, 2)

Put that in the fourth row and drag across the row.
__________________
This post was crafted using a special blend of herbs and sarcasm.
#3
05-04-2012, 10:34 AM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,609

IF(OR(SUMPRODUCT(--(A2:C2>0))=0,SUMPRODUCT(--(A2:C2>0))=3),1,2)
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#4
05-04-2012, 10:34 AM
 V1per41 Member SOA Join Date: Apr 2010 Location: Colorado Studying for job inteviews College: Purdue University Alumni Favorite beer: Easy Street Wheat Posts: 1,676

Little verbose:

=IF(A1<0,IF(A2<0,IF(A3<0,1,2),2),IF(A2>0,IF(A3>0,1 ,2),2))

Not sure what you wanted to do if they equal zero
__________________
1/P 2/FM MFE MLC C
#5
05-04-2012, 10:34 AM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,609

damn, too slow. mine is more better though

spent too long trying to figure out how to do it without the OR.
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#6
05-04-2012, 10:38 AM
 V1per41 Member SOA Join Date: Apr 2010 Location: Colorado Studying for job inteviews College: Purdue University Alumni Favorite beer: Easy Street Wheat Posts: 1,676

Quote:
 Originally Posted by ADoubleDot damn, too slow. mine is more better though spent too long trying to figure out how to do it without the OR.
When I plug yours in, I got 2 for all of them.

ETA: NM, you did A1-C1 as the range instead of A1-A3
__________________
1/P 2/FM MFE MLC C
#7
05-04-2012, 10:38 AM
 stilts3 Member Join Date: Nov 2004 Posts: 124

=IF(COUNTIF(A1:A3,">=0")*COUNTIF(A1:A3,"<0")=0,1,2 )
#8
05-04-2012, 10:38 AM
 Smart Actuary Member Join Date: Sep 2011 Posts: 7,795

i used vivi's but also want to see how adoubledots' work. be back with report.

Thanks guys.
__________________
I've always had a problem with the average macho man; they've always been a threat to me - Kurt Cobain
#9
05-04-2012, 10:38 AM
 ADoubleDot Member Join Date: Nov 2007 Location: Slightly Dusty South Studying for the rest of my life Posts: 35,609

oh, i did it for columns of numbers instead of rows

fixed: IF(OR(SUMPRODUCT(--(B1:B3>0))=0,SUMPRODUCT(--(B1:B3>0))=3),1,2)
__________________
def no_one(the_spanish_inquisition):

**** Juan.
#10
05-04-2012, 10:39 AM
 Baby, ByeBye Member CAS Join Date: Jan 2012 Location: your mom Posts: 14,091

drones

 Thread Tools Display Modes Linear Mode

 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 02:09 AM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top