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

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
Asked:
Sanjay Gandhi
3 Solutions
 
barry houdiniCommented:
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
 
omgangIT 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
 
omgangIT 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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Steven HarrisPresidentCommented:
You should use an IF statement in each column.

See the attached workbook.
Q-28316783.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Sanjay GandhiFounder, 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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now