• 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.

Excel count and conditional formating

H

hotcomper

New Member
I am try to create a formula that will count certain criteria.

This is my first post so will try my best to explain what I am trying to achieve.

I have one figure x (total leave), y (total booked), z (total remaining). These are in separtate cells. New leave is issued 1 Apr, you must have 15 days booked by 30 Apr, 20 days booked by 31 Oct and 38 by 31 Mar.

I need to be able to display this infomation as a graph/chart. I need a formula to look at the current date then compare the leave booked against the 15, if this is achieved I need a 1, if not reached a 2, if 0 days booked 3. I can then do a count of everyones leave and display it as a graph/chart. I am guessing I would have to use a different cell for each rule as in Apr, Oct then Mar?
 
I'm not sure what message you want to convey with the graph, but I think I can help o the formula issue.


As you surmised at the end, each of the following formulas can be applied to additional dates as needed. I'll refer to this in the formula as "CriteriaData".

Sounds like you have a list somewhere where you're keeping track of dates that have been booked. I'll call that "Dates"

I'll also assume that somewhere you have a table with the 3 given dates and their corresponding criteria. I'll refer to this simply as "DateTable"


Once you have that table, the formula to produce result (1, 2, or 3) is:

=IF(COUNTIF(Dates,"<="&CriteriaDate)=0,3,2-(COUNTIF(Dates,"<="&CriteriaDate)>=LOOKUP(CriteriaDate,DateTable)))
 
Thanks for quick reply, first off I have a list of each individual with the leave break down, the one the concerns me is the total leave booked, this has to be compared with todays day. If you do not have 15 days leave booked by Apr you display a 2, if you have zero days booked you 3 if you have achieved this you get a 3. This is my first problem. With this info I can display the info as a chart.

(bit awkward to to explain this without images)
 
So, list of names in col A. Then you have the 3 col's of x, y, and z (from your first post). We're concerned with y (how many booked). I'll assume this is in column C.


Now, somewhere you need to have a table correlating your key dates with the number of dates required. I'm not sure if this is your header, or what, but you can adjust formula as needed.


=IF(C2=0,3,2-(C2>=LOOKUP(CriteriaDate,DateTable)))
 
How do I generate the 1,2 or 3. I first need to compare the date against the date of Apr 30, I then need to compare the leave booked against 15. This is my problem. Once I get this sorted I think I am confident with the rest.


30 April - If you have 15 days booked should be a 1 (meaning ok), if you have 14 - 1, 2 (meaning not good), if you have 0, 3 (means bad)

31 Oct - If you have 20 days booked should be a 1 (meaning ok), if you have 19 - 16, 2 (meaning not good), if you have 15 -0, 3 (means bad)

31 Mar - If you have 38 days booked should be a 1 (meaning ok), if you have 37 - 20, 2 (meaning not good), if you have 19 -0, 3 (means bad)

I am guessing each date will have to be a separate cell for the count, the count is not the issue its how do I display the 1,2 or 3?
 
I have tried simplifying this now:


=IF(U5>=$U$102,2,)


U5 is my booked leave, $U$102 is the value 15, if the value of U5 is 15 or greater we get the value 2, this is fine, if the value is less than 15 we get a '0', I need it to display a'1' if we get a value of between 1 and 14. Is this possible?
 
I think I need 3 statements in one line:


x=15, If U5(leave booked)=>x, 2, If U5 is (between 1-14), 1, If U5 is 0, 0
 
hotcomper,


What part of this formula is not working?

=IF(C2=0,3,2-(C2>=LOOKUP(CriteriaDate,DateTable)))


Aka, what result are you getting/not getting that you want?

in layman, the formula reads:

"If c2 is 0, then display 0 (output #1). Otherwise, Find out if C2 is greater than the required number of booked dates for that criteria date. If so, the formula is 2 - 1 = 1 (output #2) otherwise, the formula is 2 - 0 = 2 (output #3)
 
You must log in or register to reply here.
Back
Top