• Status: Solved
• Priority: Medium
• Security: Public
• Views: 371

# Formula required

Hi,

I've these columns, and I want a formula in col C, D, E, and F which should have the amount shown as per days in col B. For example, for first entry, 2500 should appear in col C, because in col B, days against 2500 are 20. And fifth entry where amount is 1450, the amount should appear in col E. So i want formula in these columns so that the amount is accordingly displayed, and other cells should remain blank.

A              B              C             D              E               F
Amount      Days       0-30             31-60      61-90      91-120
2500      20
3000      35
3500      15
1580      45
1450      68
2658      70
6540      90
2540      120
3620      110
4890      55

Thanks,

- San.
0
Sanjay Gandhi
3 Solutions

Commented:
You can use AND, e.g. in C2 use this formula

=IF(AND(\$B2>0,\$B2<=30),\$B2,"")

copy that across to F2 and change the amounts in each column, e.g. D2 becomes

=IF(AND(\$B2>30,\$B2<=60),\$B2,"")

E2

=IF(AND(\$B2>60,\$B2<=90),\$B2,"")

etc.

then copy down columns as far as required

regards, barry
0

IT ManagerCommented:
Column C formula
=IF(AND(B25>=0,B25<=30),A25,)

Column D formula
=IF(AND(B25>=31,B25<=60),B24,)

Column E formula
=IF(AND(B25>=61,B25<=90),B24,)

Column F formula
=IF(AND(B25>=91,B25<=120),B24,)

OM Gang
0

IT ManagerCommented:
Sorry, should have been

Column C formula
=IF(AND(B25>=0,B25<=30),A25,)

Column D formula
=IF(AND(B25>=31,B25<=60),A25,)

Column E formula
=IF(AND(B25>=61,B25<=90),A25,)

Column F formula
=IF(AND(B25>=91,B25<=120),A25,)

OM Gang
0

PresidentCommented:
You should use an IF statement in each column.

See the attached workbook.
Q-28316783.xlsx
0

EngineerCommented:
Or use this formula in C2 and copy it down and across

=IF(AND(\$B2>=VALUE(LEFT(C\$1,FIND("-",C\$1)-1)),\$B2<=VALUE(RIGHT(C\$1,LEN(C\$1)-FIND("-",C\$1)))),\$A2,"")
0

Founder, KenhalAuthor Commented:
Incidentally first best answer by OmGang.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.