Solved

Formula required

Posted on 2013-12-12
6
304 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
6 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 50 total points
Comment Utility
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
Comment Utility
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 300 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Incidentally first best answer by OmGang.

Thanks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now