IF condition with value splitting

[Shaikh Aziz]

Dear All,

Please help me to correct below IF condition, I want to breaks value of "No. of lives" according to given financial year from start date of policy.

I have tried to solve problem with IF condition in F4 & G4 but unfortunately my formula works only for tenure 1.

My Formula : for columnF4 =D4*($F$3-A4)/E4

Conclusion: want to breaks value of "No. of lives" according to given financial year from start date of policy

Rows 1

A Column

B Column

C Column

D Column

E Column

F Column

G Column

H Column

I Column

J Column

K Column

L Column

M Column

N Column

O Column

P Column

Q Column

Rows 2

FY

FY

FY

FY

FY

FY

FY

FY

FY

FY

FY

Rows 3

POLICY_START_Date

POLICY_END_Date

Tenure

No. of lives

Total no of days

31-03-2018

31-03-2019

31-03-2020

31-03-2021

31-03-2022

31-03-2023

31-03-2024

31-03-2025

31-03-2026

31-03-2027

31-03-2028

Sum

Rows 4

24-01-2018​

23-01-2019​

1​

1​

364​

0.18​

0.82​

-0.18​

-1.19​

-2.19​

-3.20​

-4.20​

-5.20​

-6.21​

-7.21​

-8.21​

1.00​

Rows 5

24-01-2018​

23-01-2020​

2​

1​

729​

0.00​

Rows 6

05-01-2018​

04-01-2021​

3​

1​

1095​

0.00​

Rows 7

14-02-2018​

13-01-2022​

4​

1​

1429​

0.00​

Rows 8

29-12-2017​

28-12-2022​

5​

1​

1825​

0.00​

Rows 9

01-11-2018​

31-10-2025​

7​

1​

2556​

0.00​

Rows 10

29-06-2019​

28-06-2028​

9​

1​

3287​

0.00​

 

[SanjayGMusafir]

F4 =D4*($F$3-A4)/E4

=$D4*(F$3-A$4)/$E4

Problem is with Cell referencing. Check if above works for you.

 

[Shaikh Aziz]

Dear Sanjay,

I want to split no. of lives which is "1" into given FY years, e.g. formula would be, (Total no. of lives * difference of days from FY year and policy start date / Total no. of days.) I want to make this logic in IF condition respective in all tenure. e.g. 1, 2, 3, 4, 5, 7 & 9.

 

[SanjayGMusafir]

I want to make this logic in IF condition respective in all tenure. e.g. 1, 2, 3, 4, 5, 7 & 9

Somehow, I'm not able to understand what exactly you want IF function to analyse and return as result. If you may elaborate, would be happy to help you.

 

[Shaikh Aziz]

Sir, if you will look into row 4 there is 0.18 & 0.82 which sum is "1" & there is negative entries which i would considered as "0" .

My total no. of lives is 1 but i want to calculate earning lives based on given policy start days to financial year days accordingly.

 

[SanjayGMusafir]

if you will look into row 4 there is 0.18 & 0.82 which sum is "1" & there is negative entries which i would considered as "0"

Got it. Just a modification to the formula and try this and give your feedback if it works.

=IF(($D4*(F$3-A$4)/$E4)<=0,0,($D4*(F$3-A$4)/$E4))