YouTubersLoveExcel#36: VLOOKUP increase based on Rating

Hi Guys

I'm trying to come up with a document for our consultants to show them their overall ratings for the last 6 months.

They have 3 tasks to which targets are given, if they achieve, over achieve, did not meet targets they get a rating of A,B,C or D.

I need to know how to calculate their overall rating for the 3 scenarios, so if they scored an A, C and D, what is their overall rating?

I have attached a workbook.

Thanks!

Hi,

I'm new to Excel and I'm trying to develop a new risk assessment tool for work.
Currently I have managed to set my spreadsheets and worksheets up so that all the data is in the worksheet "My Data" which I have protected so only I can access it. The other worksheet called "Risk Assessment" has a drop down box where I can select a range of names. Selecting invidiual names populates two graphs with the numeric data from "My Data" and also populates cells I13:I18 with risk assessment numbers which have a conditional formatting applied to them so that if the numbers (score) are returned within a certain range the number changes from Green, Yellow or Red.
What I would like to do, if possible, is in cells J13:J18 to return a text value of either Very High, High, Slightly Raised, Close to average, Low or Very Low based on the number that is returned with each of the cells I13:I18. This would depend upon a range e.g. 20 would return the value of High, 16 would return a value of slightly raised.
I would be grateful for any help that is out there.

Stuart

Hi,

I am creating a scorecard and I'm looking for a formula that will automatically assign a 'final rating' according to the points received. I'd like to use the 1-5 range, 5 as the highest with 3 as the passing rate which is equivalent to 70 points.

Possible Points: 100
Passing Points: 70
Range: 1-5 (5 is highest)
Equivalent of 70 points is 3 in Final Rating


Help me please.

Thanks,
E.

Hi,

I have a list of issues and depending on their priority would require a different target closure date e.g. low priority = 90 days, medium = 45 days, High = 10 days.

So if I have the following columns:
Issue No (Unique Ref #) Date Raised Priority (Low/Medium/High) Target Closure (based on the pre-determined criteria as detailed in above paragraph)
Can somebody suggest a formula to enter in the "Target Closure" column so it is automated?

Many thanks in advance...!! I have more questions but i guess its best to seperate them!

Please,


ActiveCell.Value = "=VLOOKUP(a3,QuadroPacientes,2,FALSE)"

How to increase the cell A3 to A4, ...when I fill a column.

Hi, I am in the real estate business and need to create a formula based on the following logic/scenario:

We typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase.

My questions have two parts:

1. Is this something array formula can handle in one formula?

2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps.

For example,

Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30

Please note that both and month and rent are not fixed but logical values.

I guess this would be a typical multiple IFs if not for its limitation (I need 20 bumps which would be 20 arguments). I was told I may be able to do this with Name Formula or Concatenate. And I have tried the Concatenate which looks like would be a MONSTER formula with 20 OR statements in it.

Is there a way the array formula can be applied here as well? I have searched this website and found a thread containing a similar situation where a LOOKUP was used beatifully. The difference is my ranges and results are all logical values instead of fixed values.

I am looking for answers to both of my questions. If anyone can shred any light on it, that would be great. I attached the sample here. Ask if you are not clear.

Thanks a million!!!!

Hi - I need to create a macro or routine which will automatically increase a cell with numbers by "1" if an adjacent cell is greater than "0". I am always adding rows and deleting rows however so the number of rows and location is not fixed. I appreciate any help! Thanks!

What what code need to look like to search for a unique figure lets say 9525 in a specific range of Sheet 2 lets say B2-B6

if it finds this value to increase a value of sheet 1, lets say increase value of Sheet1.Range(B2) by 1 (so that if B2 = 2 currently, to have it increase by 1 to equal 3)

further more, How can i duplicate this to work in multipe ranges of Sheet 2
such as B2-B6 Then again at B7-B10 and so on. (multiple mini tables in sheet 2)

I want to check 2 cells (2 different columns) for a worksheet.

Easy to do programmatically but not familiar with spreadsheet
functions. Here's what I want to do in psuedo code...

For every row (with data) in a worksheet
If column X cell = 1 AND column Y cell >= 50
ctr = ctr + 1

Any help would be appreciated.

Mike

Hi,

I am working on a spreadheet in which the numerical value in cell A1 are copied and pasted from another worksheet. The value can increase and decrease over time.

Also, the value may sometime have a negative value hence when the numerical number gets higher with a negative sign, its decreasing, and vice versa.

I would like to know if there is a way to change the cell colour based on the increase/decrease data (e.g. green when increase, red when decrease).