Formula to calculate project number based on group

Grace N

Hi 

I want to calculate a project number based on what group it is in. In this example, there are 16 projects among 4 departments. I entered the 3rd column manually; i.e. the first digit is the department number and the second digit is the nth project for that department. What formula can I use to calculate the values in the 3rd column?

Thanks

-Grace

BernardW

Does the project number have to indicate how many projects there are in a department?  If so, why?  If not, I would use the auto number functionality and prefix/append it with the department and formatting to obtain the desired results.  For example: <Dept#>-<auto num>

Paul Newcome

You would first need to establish which department is which department number. If you are actually using "Department #", then you can use a RIGHT function.

 

=RIGHT(Department@row, 1)

.

If you are using actual department names such as "Admin", "Production", etc, you will want to write a nested IF statement to generate the appropriate number. For example...

 

=IF(Department@row = "Admin", "1", IF(Department@row = "Production", "2", ...............................))))

.

 NOTE: I intentionally put quotes around the numbers in the IF statement and left the RIGHT function as is to generate a text value as opposed to a numerical value.

.

Next we will add in the ".".

 

=first_formula_choice + "."

.

Then we add the count for the number of projects for that department.

 

first_formula_choice + "." + COUNTIFS(Department$1:Department@row, Department@row)

.

NOTE: The $ before the row reference to lock in row 1 as the start of your range.

.

Let me know how that works out for you.

Grace N

Thanks, Paul. Your formula works for me. 

I have a follow up question. I want to have 2 digits to the right of the decimal point. I modified your formula as follows: 

=RIGHT(Department24, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0" + COUNTIFS(Department$1:Department@row, Department@row), COUNTIFS(Department$1:Department@row, Department@row))

Is there a more elegant solution?

-Grace

Grace N

Oops, my formula should be 

=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0" + COUNTIFS(Department$1:Department@row, Department@row), COUNTIFS(Department$1:Department@row, Department@row))

Paul Newcome

There isn't much in the way of "elegance" that can be added other than simplifying the IF(COUNTIFS portion just a little.

 

Think of it this way...

 

If you leave the third portion of an IF statement empty, then you would have a potential of two different results. Either the specified result if true, or blank.

.....................IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0" + COUNTIFS(Department$1:Department@row, Department@row), COUNTIFS(Department$1:Department@row, Department@row)..............

 

You can actually use an IF statement to say if the COUNTIFS is less than 10, "0". If it's true, then you get a 0. If it isn't, it leaves a blank. Then you add your COUNTIFS regardless of what it is.

 

.....................IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0") + COUNTIFS(Department$1:Department@row, Department@row).............

 

Which would leave you with:

=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0") + COUNTIFS(Department$1:Department@row, Department@row))

.

Saves a few keystrokes but does the same exact thing.

 

I use this idea frequently with a nested IF to account for numbers under 10000.

 

......IF(COUNTIFS(.......................) < 10, "000", IF(COUNTIFS(.......................) < 100, "00", IF(COUNTIFS(.......................) < 1000, "0"))) + COUNTIFS(.......................)......

Grace N

Thanks again, Paul

I got an INCORRECT ARGUMENT error due to an extra ")" in your formula. This is the corrected formula

=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0") + COUNTIFS(Department$1:Department@row, Department@row)

-Grace

Paul Newcome

Sorry about that. Glad you were able to get it working. Happy to help!