Re: Lookup Based On Criteria and Populate
bump.
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Re: Lookup Based On Criteria and Populate
bump.
Re: Lookup Based On Criteria and Populate
bump.
Re: Lookup Based On Criteria and Populate
Hi,
I tried changing the parameters and the following is what happened:
1) Nothing changed in the data populated. It is the same data as per the attachment
2) No stock name is populated in cell B15 downwards.
For example, I entered more than $5.70 and less than $7.35 for last done and the following stock and the relevant data should have have populated:
[TABLE="width: 923"]
[tr][td]Name
[/td][td]Last Done
[/td][td]Market Cap (M)
[/td][td]PE
[/td][td]Price/NAV
[/td][td]High, 52 Wks
[/td][td]Low, 52 Wks
[/td][td]Revenue Growth
[/td][td]Current Ratio
[/td][td]Debt to Equity
[/td][td]ROA
[/td][/tr][tr][td]AMMB
[/td][td]$ 5.76
[/td][td]$ 17,361.71
[/td]
[TD="align: right"]12.886[/TD]
[TD="align: right"]1.633[/TD]
$ 7.05
[/td][td]$ 5.30
[/td]
[TD="align: right"]10%[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]2%[/TD]
CIMB
[/td][td]$ 7.27
[/td][td]$ 54,036.27
[/td]
[TD="align: right"]14.837[/TD]
[TD="align: right"]2.156[/TD]
$ 9.01
[/td][td]$ 6.56
[/td]
[TD="align: right"]3%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]1%[/TD]
RHBCAP
[/td][td]$ 7.31
[/td][td]$ 16,022.91
[/td]
[TD="align: right"]11.076[/TD]
[TD="align: right"]1.435[/TD]
$ 10.40
[/td][td]$ 6.53
[/td]
[TD="align: right"]6%[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]3%[/TD]
[/TABLE]
However, when I clicked on the macro button, nothing happened.
I have a stock data in sheet 1 as follows:
Last Done
Market Cap (M)
PE
Price/NAV
High, 52 Wks
Low, 52 Wks
Revenue Growth
Current Ratio
Debt to Equity
ROA
In sheet 2, I have set a parameter table (cell c3 to c12) to enter a range of criteria. Based on these range of criteria entered in parameter table, I would like to populate all relevant stocks in cell b15 downwards and their respective data in cell b15 to l15 downwards in sheet 2 from sheet 1 that satisfies the criteria in the parameter table.
Is there a way to create a formula to achieve this?
Appreciate all help.
I have a cross post here:
http://%22http//www.mrexcel.c…php?t=605515%22http://www.excelforum.com/excel-worksheet-functions/809635-lookup-based-on-criteria-and-populate.html</a>[/INDENT]
[/INDENT]
Re: Time Formula
bump
Re: Time Formula
Hi,
I hope I am getting this right.
The downtime occurred on Feb 14 on 8.58 pm. The operating hours for this branch on Monday is between 9.30 am to 10.30 pm. For Feb 14, based on the operating hours on Monday, the downtime only affected the outlet from 8.58 pm to 10.30 pm which is 1 hour and 32 minutes.
On Feb 15, the operating hours for the branch is still from 9.30 am to 10.30 pm. Therefore it is 13 hours.
The downtime finally stopped on Feb 16 at 9.06 am. However, this did not affect the branch since the branch only opens at 9.30 am on Feb 16 which is a Wednesday.
Therefore, in total, the downtime hours that affected the operating hours of the branch is 14 hours and 32 minutes.
Hi,
I have the following sheet that calculates the downtime in branches based on the specific operating hours everyday.
The downtime ahould only take into account during the operating hours and should exclude hours of downtime during non operating hours.
In the example attached, the actual downtime during operating hours is 14 hours and 32 minutes. Although the downtime lasted from Feb 14 to Feb 16, the downtime hours that affected operating hours was 14 hours 32 minutes and not 36 hours and 8 minutes.
Is there anyway we could build a formula for this ?
Appreciate all the help.
I have a cross pots here:
Re: Change PivotChart Numbers To Percentage
Hi PCI,
I understand your solution.
Can you guide me how you did that ?
Re: Change PivotChart Numbers To Percentage
Hi,
My apologies. I think I might not have been clear.
In my pivot table, I only need two data to be in % as below:
1) Jan Error Points >>This should be in normal numerical . Example is 1.25
2) Feb Error Points >>This should be in normal numerical . Example is 1.25
3) Jan Q3 Score >> This should be in %
4) Feb Q3 Score >> This should be in %
The pivot chart should be as per the pivot table above in which error point should be in normal numerical and Q3 score should be in %.
Based on the above, I changed data # 3 and 4 to % in the pivot table by right click>Numers>Percentage.
However, the chart still shows say example 0.9397 for Jan Q3 Score and Feb Q3 Score instead of % as per the pivot table , say example 93.97%.
Is there a way to do this ?
Re: Change Pivotchart Data To Percentage
Hi,
How do I change the X axis to % as you have done ?
Hi,
I have a pivot chart that I have changed the data in the pivot table to % for Jan Q3 Score and Feb Q3 score but the chart seems to still display in numerical.
Appreciate assistance on how I can get the chart to display as % for Jan Q3 Score and Feb Q3 Score per the pivot table.
Re: Calculate Percent Increase Based On Matrix
That worked !
Thank you Krishnakumar and Doeminic.
Really aprreciate your help.
Re: Calculate Percent Increase Based On Matrix
Hi,
For the bonus grid, if the staff has 3 months or less, then we would not pay any bonus.
I tried expanding the Lookup formula and adjusting the grid but it still states "N/A".
Example is cell K10, the staff has a job time of 3 months. Therefore, based on the bonus grid, he will not receive any bonus.
Appreciate assistance.
Re: Calculate Percent Increase Based On Matrix
Hi,
I am not sure how to use vlookup on this one. The vloopup reads based on one criteria.The scenario that I have has multiple criteria.
For example,
1) On column I, for Abraham, since he has a 2 rating and a 78% ratio, he should get a 13% salary increase based on the salary grid which shows that a 2 rating with a ratio of 75% to 79% is 13% increase in salary.
2) On column J, for Abraham, since he has a job time of 43 months, the bonus grid indicates that with a 2 rating and a job time of more than 18 motnhs, he should get 24.99% of bonus.
Is is possible to use vlookup for both the criteria above or any other formula? I tried IF and AND but it I am not getting it right.
Appreciate assistance.
Hi,
I need to calculate the % of salary increase and bonus for staff based on a matrix.
1) The salary increase in based on the salary grid. Each staff is allocated a ratio as shown in cell H4 to H25. For example, for Abraham, he has obtained a rating of 2 and has a ratio of 78%. Looking at the salary grid, he should get a 13% salary increase.
2) The bonus is also based on a bonus grid. The bonus is based on the rating and job time.The date used for current date calculation is Jan 11 2008. For example, Abraham has a rating of 2 and a job time more than 18 months.Therefore, he should get a bonus of 24.99% based on the bonus grid.
I am trying to create a formula in salary increase% column (yellow) and the bonus % column (blue) to automatically update from the salary grid and bonus grid. The actual staff number is quite long and using a correct formula would ensure the data ia accurate based on the grid/matrix.
Appreciate any help.
Re: Populate Data From Another Sheet With Criteria
Thank you ! That worked.
Hi,
I hope the thread title is correct.
I have a spreadsheet which I need to populate the data from sheet based on current date.
Cell C7 in the "Staff" tab needs to update from tab "Data" for John based on the current date . The current date is located in cell A3 in the "Staff" tab.
On daily basis, as I open the spreadsheet , cell C7 should update from the "Data" tab automatically based on the date in cell A3 in the "Staff" tab.
Example, on January 4 2008, cell C7 should populate as 2 from the "Data" tab.
I tried using the IF formula, but I cant expand the formula for the whole month of January since it is limited only to 7 arguments.
Appreciate help.
Hi ,
I am working on creating an examination questions in Excel. I want to also build in formulas in the Excel sheet so that the answers will be calculated automatically as the candidates choose the answers.
I have inputed an option button from the froms toolbox beside each answers for the candidate to click to indicate the answers.
Is it possible to calculate the correct answer or wrong answer with a formula when the candidate clicks on the option button ? Is there a formula that I can use ?
Appreciate assistance.
Re: Formula Based On Multiple Criteria
Thank you Richard. That worked. But can you explain how the formula returns 1 and 0 when I dont see any 1 or 0 in the formula ?
Re: Formula Based On Multiple Criteria
Hi Richard,
I tried the formula but it does not populate 1 or zero. I am attaching an example.
On cell H8, the answer should be 1 ( if both answer 1 and answer 2 is "correct")
In cell H40 the answer should be 0 ( if answer 1 is "correct" and answer 2 is blank)
Appreciate ur help.