Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Formula required

Posted on 2013-12-12
6
Medium Priority
?
365 Views
Last Modified: 2013-12-12
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
Comment
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
  • Learn & ask questions
6 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 200 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

by:omgang
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

by:
omgang earned 1200 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 200 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

by:Saqib Husain, Syed
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

by:sanjay-gandhi
ID: 39714723
Incidentally first best answer by OmGang.

Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question