Solved

# Formula required

Posted on 2013-12-12
351 Views
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
Question by:sanjay-gandhi
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Assisted Solution

barry houdini earned 50 total points
ID: 39714622
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

LVL 28

Expert Comment

ID: 39714625
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

LVL 28

Accepted Solution

omgang earned 300 total points
ID: 39714629
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

LVL 18

Assisted Solution

Steven Harris earned 50 total points
ID: 39714633
You should use an IF statement in each column.

See the attached workbook.
Q-28316783.xlsx
0

LVL 43

Expert Comment

ID: 39714679
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

Author Closing Comment

ID: 39714723
Incidentally first best answer by OmGang.

Thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook Free & Paid Tools
This article describes a serious pitfall that can happen when deleting shapes using VBA.