• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help with "AND and/or OR Function

L

LaurenL

New Member
I have attached a sample of what I am working on; in COLUMN R: the formula is correct; I now need to also add in an AND or OR (I think that is correct): so that if D2="Hybrid - Hearings" then the formula will determine the value based on <2 instead of <4 as the formula stands now.

Any assistance is always appreciated! thank you
 

Attachments

  • Frequency working doc.xlsx
    355.5 KB · Views: 4
LaurenL said:
I have attached a sample of what I am working on; in COLUMN R: the formula is correct; I now need to also add in an AND or OR (I think that is correct): so that if D2="Hybrid - Hearings" then the formula will determine the value based on <2 instead of <4 as the formula stands now.

Any assistance is always appreciated! thank you
Click to expand...

And the Password is?!
 
The lookup table you're referring to on sheet Frequency Source - TPH contains one value, however you're referring to a range of some 3500 cells.

It's always best to make lookups into Tables, so that if the number of lookups changes, the Table expands or contracts without the need to re-write a formula.

Your Performance Table appears to be a dump from an exterior source when you unhide cols H->P, however the value of Transactions per hour is not calculated, and it's not apparent how you arrive at it, given the other data points shown.

The formula in Column R is working as written - although I must admit to not understanding the purpose of the Calculation. Explain the calculation you're having issues with - and where you want the answer returned.

If you frame the question clearly, folks around here can invariably provide a solution, has been my experience.
 
I will do my best to explain better:

The lookup table you're referring to on sheet Frequency Source - TPH contains one value, however you're referring to a range of some 3500 cells.
The file I attached is a copy of the original; I removed the other data from this source.

It's always best to make lookups into Tables, so that if the number of lookups changes, the Table expands or contracts without the need to re-write a formula.
The table reference up to 3500 cells so the formula doesn't have to be re-written

Your Performance Table appears to be a dump from an exterior source when you unhide cols H->P, however the value of Transactions per hour is not calculated, and it's not apparent how you arrive at it, given the other data points shown.
Yes, what I attached is a copy of the original spreadsheet; I meant to delete the columns I was not referencing for assistance. The Transactions per hour is calculated in the original file.

The formula in Column R is working as written - although I must admit to not understanding the purpose of the Calculation. Explain the calculation you're having issues with - and where you want the answer returned. The purpose of the calculation is based on internal metrics & is used to determine a trend of meeting or not meeting a KPI. I want the answer to return R2. The formula as it stands is correct, however, due to various teams and a difference in each teams' KPI expectation, I would like the formula to also consider their team name (Hybrid -Hearings) and if that specific team name is found in cell D2 then I need the formula to reconfigure to <2 as opposed to how it is written currently at <4.

If you frame the question clearly, folks around here can invariably provide a solution, has been my experience. I hope the explanation above was clearer to assist.

Thank you,
 
The current formula in R2 is as follows
=IF(Q2<4,(VLOOKUP(F2,'Frequency Source - TPH'!$A$1:$B$3475,2,FALSE)+(COUNTIF(Q2,"<4"))),0)

If you want the formula to be dependent on the team name, e.g. if the team name is Hybrid-Hearings the inequality you're testing becomes Q2<2, why not create a separate lookup table with Team Names and the value you want them to meet or exceed? Then refer to that value in your formula.

>>The table reference up to 3500 cells so the formula doesn't have to be re-written<<
That will work some of the time, but it's not the best use of excel resources, and it will cause issues.
 
You must log in or register to reply here.
Back
Top