I am trying to find a ratio in which one of the numbers is negative. My example is as follows: My revenue is up 40% and my Expenses are down -6.5%. What is that ratio? I cannot figure out how to do this in Excel.

]]>Hi guys,

Always like a spreadsheet with nice aesthetics, just mulling over the best way to merge F3, G3, H3, I3, J3 into K3.

I've had a crack, but I'm little scared to go any further for the fear of breaking something!

Ty guys

Mdn

Always like a spreadsheet with nice aesthetics, just mulling over the best way to merge F3, G3, H3, I3, J3 into K3.

I've had a crack, but I'm little scared to go any further for the fear of breaking something!

Ty guys

Mdn

Hello,

I am trying to replace a pivot that was summarizing payments by multiple criteria's using SUIMFS, with additional countifs to get rid of duplicates and account for unique situations.

Essentially what I am trying to achieve is get rid of this pivot table altogether and hardcode formulas in the sheet to replace to collapsed view the pivot was giving.

Pivot sum.PNG

I have replaced the Pivot sum with a sumifs on the worksheet.

The SUMIFS is working except when you drag the cell down it will duplicate the values, Example below is the $8,704.11 in AP13, and then again repeated $8704.11 in AP15. The true amount that month is $8704.11, the fill down formula just duplicated it.

So as you can see the formula I have in AR13 is working by putting an 'x" to unique value and "xx" for duplicates. And we can see it working as the $8,704.11 SUMIFS formula is only being moved to Column AS once, and not twice.

countifs of sumifs.PNG

if dup then do this.PNG

the second part is now I also need to account for the below issue with duplicate values. These are not truly duplicates as you can see it is the monthly sum. They just happen to be the same amount so the above formula is reading them like they are duplicates but they are not. So in this case I would need the all those $67.85 amounts in column AP to be put in column AS. But you can see that the formula called them out as "xx" so it wont pick them up as unique values. How do I work that in the formula?

not dups.PNG

Pivot sum.PNG

]]>I am trying to replace a pivot that was summarizing payments by multiple criteria's using SUIMFS, with additional countifs to get rid of duplicates and account for unique situations.

Essentially what I am trying to achieve is get rid of this pivot table altogether and hardcode formulas in the sheet to replace to collapsed view the pivot was giving.

Pivot sum.PNG

I have replaced the Pivot sum with a sumifs on the worksheet.

The SUMIFS is working except when you drag the cell down it will duplicate the values, Example below is the $8,704.11 in AP13, and then again repeated $8704.11 in AP15. The true amount that month is $8704.11, the fill down formula just duplicated it.

So as you can see the formula I have in AR13 is working by putting an 'x" to unique value and "xx" for duplicates. And we can see it working as the $8,704.11 SUMIFS formula is only being moved to Column AS once, and not twice.

countifs of sumifs.PNG

if dup then do this.PNG

the second part is now I also need to account for the below issue with duplicate values. These are not truly duplicates as you can see it is the monthly sum. They just happen to be the same amount so the above formula is reading them like they are duplicates but they are not. So in this case I would need the all those $67.85 amounts in column AP to be put in column AS. But you can see that the formula called them out as "xx" so it wont pick them up as unique values. How do I work that in the formula?

not dups.PNG

Pivot sum.PNG

I've been asked to find a way in excel to find out when an employee (each one has a unique employee id) has an overlap in their training times (2/21/2018 2:45 PM 2/21/2018 2:53 PM) << shown in two separate columns and if it overlaps with an absence code so their training time can be rescheduled. I was thinking pasting the absence stuff in one tab and then the training times in the second tab and then have the results in a third tab which would show the cross-reference results. Any help would be great.

]]>I created a simple conditional formatting rule. Hightlight cells equal to 0 (zero). Sounds simple right, but its also highlighting blank cells red. How do I make it only highlight 0 and not also blanks?

Capture.PNG

]]>Capture.PNG

Good day,

I know the basic of formulas but what im asking assistance for I have no idea how to do.

In Monthly spreadsheet in A2 I want to type the employee number in, using this information I want the rest of the cells in row 2 to be filled with data linked to the employee. Employee data is found on multiple sheet in another workbook.

Monthly Data File:

Column:

A: EMPLOYEE NUMBER - I type in

B: INITIALS - Extract from Employee Worksheet (must have spaces if double initials)

C: FIRST NAME - Extract from Employee Worksheet (must not be in all in uppercase)

D: LAST NAME - Extract from Employee Worksheet (must not be in all in uppercase)

E: ID NUMBER

F: RACE - Extract from Employee Worksheet (must be typed in a specific way ie. "W" must be "W - White"; "I" must be "I - Indian"; "A" must be "A - African"

G: GENDER - Extract from Employee Worksheet (must be typed in a specific way ie. "M" must be "M - Male"; "F" must be "F - Female"

H: EE CATEGORY - formula based on cell J

I: DESIGNATION - Extract from Employee Worksheet (must not be in all in uppercase)

J: JOB GRADE - Extract from Employee Worksheet (single digits must have a "0" in front of digit ie, "7" must be "07")

The Employee workbook has 13 Sheets with multiple rows in them the column headings are: All text in this worksheet is in uppercase

A: CompanyNumber

B: EmployeeCode

C: Initials

D: FullNames

E: Surname

F: IDNumber

G:GroupCode

H: Gender

I: EmployeeStatus

J: DateEngaged

K: TerminationDate

L: PaypointCode

M: Paypoint

N: JobTitleCode

O: OccupationalLevy

P: JobGradeCode

Q: Job Title

The Links:

Monthly Data File /// Employee Data File

A: EMPLOYEE NUMBER - I type in /// Links to B: EmployeeCode

B: INITIALS /// C: Initials (Typed as DD need it to have a space - D D

C: FIRST NAME /// D: FullNames (All upper case only need first letter uppercase the rest lower case

D: LAST NAME /// E: Surname (All upper case only need first letter uppercase the rest lower case

E: ID NUMBER /// F: IDNumber

F: RACE /// G:GroupCode Typed as W;I;A;C need it to show A - African; C - Coloured; I - Indian; W - White

G: GENDER ///H: Gender Typed as M;F need it to show "M - Male"; "F - Female"

H: EE CATEGORY - formula based on cell J

I: DESIGNATION ///Q: Job Title (All upper case only need first letter uppercase the rest lower case

J: JOB GRADE ///P: JobGradeCode (single digits must have a "0" in front of digit ie, "7" must be "07")

I hope that you fully understand what I require based on the above explanation.

Regards

]]>I know the basic of formulas but what im asking assistance for I have no idea how to do.

In Monthly spreadsheet in A2 I want to type the employee number in, using this information I want the rest of the cells in row 2 to be filled with data linked to the employee. Employee data is found on multiple sheet in another workbook.

Monthly Data File:

Column:

A: EMPLOYEE NUMBER - I type in

B: INITIALS - Extract from Employee Worksheet (must have spaces if double initials)

C: FIRST NAME - Extract from Employee Worksheet (must not be in all in uppercase)

D: LAST NAME - Extract from Employee Worksheet (must not be in all in uppercase)

E: ID NUMBER

F: RACE - Extract from Employee Worksheet (must be typed in a specific way ie. "W" must be "W - White"; "I" must be "I - Indian"; "A" must be "A - African"

G: GENDER - Extract from Employee Worksheet (must be typed in a specific way ie. "M" must be "M - Male"; "F" must be "F - Female"

H: EE CATEGORY - formula based on cell J

I: DESIGNATION - Extract from Employee Worksheet (must not be in all in uppercase)

J: JOB GRADE - Extract from Employee Worksheet (single digits must have a "0" in front of digit ie, "7" must be "07")

The Employee workbook has 13 Sheets with multiple rows in them the column headings are: All text in this worksheet is in uppercase

A: CompanyNumber

B: EmployeeCode

C: Initials

D: FullNames

E: Surname

F: IDNumber

G:GroupCode

H: Gender

I: EmployeeStatus

J: DateEngaged

K: TerminationDate

L: PaypointCode

M: Paypoint

N: JobTitleCode

O: OccupationalLevy

P: JobGradeCode

Q: Job Title

The Links:

Monthly Data File /// Employee Data File

A: EMPLOYEE NUMBER - I type in /// Links to B: EmployeeCode

B: INITIALS /// C: Initials (Typed as DD need it to have a space - D D

C: FIRST NAME /// D: FullNames (All upper case only need first letter uppercase the rest lower case

D: LAST NAME /// E: Surname (All upper case only need first letter uppercase the rest lower case

E: ID NUMBER /// F: IDNumber

F: RACE /// G:GroupCode Typed as W;I;A;C need it to show A - African; C - Coloured; I - Indian; W - White

G: GENDER ///H: Gender Typed as M;F need it to show "M - Male"; "F - Female"

H: EE CATEGORY - formula based on cell J

I: DESIGNATION ///Q: Job Title (All upper case only need first letter uppercase the rest lower case

J: JOB GRADE ///P: JobGradeCode (single digits must have a "0" in front of digit ie, "7" must be "07")

I hope that you fully understand what I require based on the above explanation.

Regards

1.png

I have simplified a bigger problem I have simplified the problem here. Your help would be greatly appreciated. What is most important to me is a simple shortest solution.

I have to arrays:

Array 1 - A1:A14 - These are the company names

Array 2 - B1:B14 - These are the products that the company create.

I have been trying to create a formula that says if A1:A14 is "Heinz" then look in the corresponding array if it says specified lables then count it. It this case I want it to count "Mummys Sauce", "Daddies Sauce", "Couzin Sauce"

So in this simplified example the the answer would Count and equal the answer = 10

Ialready have two solutions but they are not wuite correct

***DOES NOT WORK

=SUMPRODUCT(--(A1:A14 ="Heinz"), --(B1:B14 = {"Mummys Sauce","Daddies Sauce","Couzin Sauce"}) )

If I could get this style of formula working it but be EXACTLY what I need. The reason is because I reference external sheets on real solution the linkage because absolutely unmanageable. Im ideally looking for this to work.

***WORKS:

=SUMPRODUCT(--(A1:A14 ="Heinz"), --((B1:B14 ="Mummys Sauce") + (B1:B14 ="Daddies Sauce") + (B1:B14 ="Couzin Sauce") >0) )

This solution does work but Ive tried using it. But when I add all the linkage it becomes massive. Each cell 10 pages long. Hence me wondering if the first formula can be fixed

]]>I have simplified a bigger problem I have simplified the problem here. Your help would be greatly appreciated. What is most important to me is a simple shortest solution.

I have to arrays:

Array 1 - A1:A14 - These are the company names

Array 2 - B1:B14 - These are the products that the company create.

I have been trying to create a formula that says if A1:A14 is "Heinz" then look in the corresponding array if it says specified lables then count it. It this case I want it to count "Mummys Sauce", "Daddies Sauce", "Couzin Sauce"

So in this simplified example the the answer would Count and equal the answer = 10

Ialready have two solutions but they are not wuite correct

***DOES NOT WORK

=SUMPRODUCT(--(A1:A14 ="Heinz"), --(B1:B14 = {"Mummys Sauce","Daddies Sauce","Couzin Sauce"}) )

If I could get this style of formula working it but be EXACTLY what I need. The reason is because I reference external sheets on real solution the linkage because absolutely unmanageable. Im ideally looking for this to work.

***WORKS:

=SUMPRODUCT(--(A1:A14 ="Heinz"), --((B1:B14 ="Mummys Sauce") + (B1:B14 ="Daddies Sauce") + (B1:B14 ="Couzin Sauce") >0) )

This solution does work but Ive tried using it. But when I add all the linkage it becomes massive. Each cell 10 pages long. Hence me wondering if the first formula can be fixed

This is confusing so I attached a spreadsheet. Basically, I have location codes and I need each person listed once for each location code and I can't list the Vacancies. I end up using these people for a different file and I also need to use it to count how many people are at each location code. I know the simple ways of doing this if I only needed one thing. However, I need to use it for so many different files that I'd like to pull the detail back in this manner. Please see attached file and tell me if this is possible?

Thanks so much in advance!!

Thanks so much in advance!!

Hello,

On my 'Paystubs' sheet I'm trying to get the YTD gross pay from the 'YTD Log' for regular hours (times cell F5 for pay rate) and overtime hours (times cell F5 times 1.5) for employee referencing cell E3 for their ID.

My formula in H6 works using helper cells (K3 and K4) but for only the first employee.

Does someone know of a better way?

Thank you so much,

Billy

On my 'Paystubs' sheet I'm trying to get the YTD gross pay from the 'YTD Log' for regular hours (times cell F5 for pay rate) and overtime hours (times cell F5 times 1.5) for employee referencing cell E3 for their ID.

My formula in H6 works using helper cells (K3 and K4) but for only the first employee.

Does someone know of a better way?

Thank you so much,

Billy

Hi,

I have been following instructions found on a website for developing an MRP system which up until now has been great. However I have come unstuck with the following formula =IF(RIGHT(B2,5)="Total",0,G1+F2-E2) this code is placed in G2 in the following example. I need help understanding what each part of the formula does so I can adapt it for my own spreadsheet.

Any help would be greatly appreciated.

Screenshot 2018-02-22 at 18.28.30.png

]]>I have been following instructions found on a website for developing an MRP system which up until now has been great. However I have come unstuck with the following formula =IF(RIGHT(B2,5)="Total",0,G1+F2-E2) this code is placed in G2 in the following example. I need help understanding what each part of the formula does so I can adapt it for my own spreadsheet.

Any help would be greatly appreciated.

Screenshot 2018-02-22 at 18.28.30.png

Hello again guys,

(Lee if you're reading this sorry if you're tearing your hair out at me!)

I just wondered why I5 is returning a Div/0 for certain combinations that should be "Winners".

Thanks

Mdn

]]>(Lee if you're reading this sorry if you're tearing your hair out at me!)

I just wondered why I5 is returning a Div/0 for certain combinations that should be "Winners".

Thanks

Mdn

Hello all,

I have this formula in C5 on 'YTD Log' sheet but it isn't working:

I want to fill the hours for all EMPs from the 'Log' sheet please.

Does anyone know how to do this?

Thank you,

Billy

I have this formula in C5 on 'YTD Log' sheet but it isn't working:

Formula:

=IFERROR(INDEX(Log!G$5:G$371,MATCH(1,(C$3=Log!$C$3:$CD$3)*($B5=Log!$B$5:$B$371),0)),"")

I want to fill the hours for all EMPs from the 'Log' sheet please.

Does anyone know how to do this?

Thank you,

Billy

Hello

I have been trying to use that formula

=STDEV.S(IF('used data'!C:C="MALE";'used data'!U:U))

On 'used data'!C:C I have the gender, and on 'used data'!U:U I have the values I want to find the standard deviation. Apparently, the formula works fine if there are no blanks, but when there are blanks in the 'used data'!U:U column, if seems to include the blanks in the calculation (I have no blanks in the Gender column, and I tested it by removing the blanks on my values column manually).

Then, I have tried

=STDEV.S(IF(AND('used data'!U:U<>"";'used data'!C:C="MALE");'used data'!U:U))

=STDEV.S(IF(AND('used data'!U:U>0;'used data'!C:C="MALE");'used data'!U:U))

I have tried both with and without ctrl+shift+enter, to no avail.

Does anyone know how to solve this problem?

Best regards,

Diogo

]]>I have been trying to use that formula

=STDEV.S(IF('used data'!C:C="MALE";'used data'!U:U))

On 'used data'!C:C I have the gender, and on 'used data'!U:U I have the values I want to find the standard deviation. Apparently, the formula works fine if there are no blanks, but when there are blanks in the 'used data'!U:U column, if seems to include the blanks in the calculation (I have no blanks in the Gender column, and I tested it by removing the blanks on my values column manually).

Then, I have tried

=STDEV.S(IF(AND('used data'!U:U<>"";'used data'!C:C="MALE");'used data'!U:U))

=STDEV.S(IF(AND('used data'!U:U>0;'used data'!C:C="MALE");'used data'!U:U))

I have tried both with and without ctrl+shift+enter, to no avail.

Does anyone know how to solve this problem?

Best regards,

Diogo

I am having trouble with my formulas. I am trying to put a count total of J18:231 in cell C3 if dates of B18:231 fall within the month of January. However, the J cells of J18:J231 already have a lookup formula in them calculating columns f,g,h,i. Here is the formula in J

=LOOKUP(2,1/ISTEXT(F19:I19),F19:I19)

I have tried sum product and sum if formula for C3 but it is having issues reading J cells lookup.

Any suggestions on how this can be done.

Thanks,

Karen

=LOOKUP(2,1/ISTEXT(F19:I19),F19:I19)

I have tried sum product and sum if formula for C3 but it is having issues reading J cells lookup.

Any suggestions on how this can be done.

Thanks,

Karen

Hello All

Could you please help me with Sales Incentive slab and calculations

Thanks

Could you please help me with Sales Incentive slab and calculations

Thanks