• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Question Help!!

Qurat

New Member
I want to create the formula of, i am stuck in this formula making.

If i change the group class the formula apply direct pick ratio from premium trend group wise and yellow highlighted manual multiplication is difficult for me, can any one make the formula when I change the group class automatic pick the ratio from group class and make the values.

Excel sheet is attached for reference.
 

Attachments

  • Formula Making.xlsx
    22.2 KB · Views: 7
Maybe,

In O5, formula copied across and down :

=IFERROR($C5*INDEX($B$27:$H$33,MATCH($A5,$A$27:$A$33,0),MATCH("Year-"&(COUNTIF($D5:$N5,">0")+COLUMN(A$1)),$B$26:$H$26,0)),"")

Regards
Bosco
 

Attachments

  • Formula Making(BY).xlsx
    23.8 KB · Views: 6
If 2019-2014=5 so we don't apply year 6th year percentage we apply this =C11-sum($D11:N11) and if 2020-2015=5 same apply =C12-sum($D12:O12) and for previous underwriting and future underwriting year example is highlighted in grey and rest of formula is same, please put the formula like this please help bosco
 

Attachments

  • Formula Making(BY) (1).xlsx
    24.1 KB · Views: 2
Last edited:
If 2019-2014=5 so we don't apply year 6th year percentage we apply this =C11-sum($D11:N11) and if 2020-2015=5 same apply =C12-sum($D12:O12) and for previous underwriting and future underwriting year example is highlighted in grey and rest of formula is same, please put the formula like this please help bosco
Then, added one condition and replaced the formula by

In O5 copied across and down :

=IF(COUNTIF($D5:N5,">0")>=5,$C5-SUM($D5:N5),IFERROR($C5*INDEX($B$27:$H$33,MATCH($A5,$A$27:$A$33,0),MATCH("Year-"&(COUNTIF($D5:$N5,">0")+COLUMN(A$1)),$B$26:$H$26,0)),""))

Regards
Bosco
 

Attachments

  • Formula Making(BY1).xlsx
    25.8 KB · Views: 2
Back
Top