how to calculate aggregate [SOLVED]

[rahulsharma012]

Hi,


i want that if a student passed in 4 or more subjects it should show the aggregate result in cell d9 as pass else if student failed in more than 4 subjects than it should show fail in d9.

link for sheet1 problem mentioned below.

http://www.fileconvoy.com/dfl.php?id=g7c8f3e50d469eb67999319889c376d58234426a54


2nd problem:

in sheet2 i want name of sachin tendulkar to be changed with tendulkar sachin


either it can be done with a single formula for reversing or user defined formulas.


regards,

Rahul

 

[Hui]

Rahul


Sheet1 D9:

=IF(COUNTIF(D2:D7,"Pass")>3,"Pass","Fail")


Sheet2 B2:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))&" "&LEFT(A2,FIND(" ",A2))

 

[NARAYANK991]

Hi Rahul ,


The worksheet you have uploaded has several names against the subjects ; is this correct ?


If all the names are the same , and the grades are for one student for the different subjects , then you can use the formula :


=IF(COUNTIF(D2:D7,"PASS")>=4,"PASS","FAIL")


For the second problem , you can take your pick from the following 2 :


=RIGHT(A2,LEN(A2)-FIND(" ",A2)) & " " & LEFT(A2,FIND(" ",A2)-1)


=MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2))


Narayan

 

[rahulsharma012]

thanks a lot hui and narayank991 for replying "FAST" which is not under the rules on this site.thanks a lot.

 

[Hui]

Rahul

Replying fast isn't under the rules, as if we don't reply fast we're not breaking the rules!


Narrayn

I like that second formula for Q2

I'd never thought of tackling it that way!

 

[webmax]

hi narayanan


if i have the three word name Sachin Ramesh Tendulkar i want this reverse as Tendulkar Ramesh Sachin how to do this.

 

[Hui]

Webmax


Firstly, can you please start your own question as even though this question is similar it is different

Please read the site rules at: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting


You could try:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))&MID(A1,FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1))

 

[NARAYANK991]

Hi ,


You can also learn the following standard technique :


=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*2,99) & MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99) & MID(SUBSTITUTE(A1," ",REPT(" ",99)),1,99))


You can see the principle here ; suppose you want to extend this to say 4 names , such as A B C D , which you want in the reverse order D C B A , all you have to do is add the additional MID construct at the beginning like this :


=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*3,99) & MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*2,99) & MID(SUBSTITUTE(A1," ",REPT(" ",99)),99,99) & MID(SUBSTITUTE(A1," ",REPT(" ",99)),1,99))


Narayan

 

[webmax]

thanks narayan