Hello, First I have to admit I am not a super excel user in fact pretty much a hack trying to learn on the fly. That being said I am trying to create a formula use IF based on dates to return one of 3 results. Basically I am trying to incorporate the following:

Projected completion date > Actual Date it would return one value, if it was < it would return a different value, and if it was >but -5 would return yet another value. The values are just text

In this example I am returning a value of "Yes" but I am not sure how or if I can accomplish the other 2 returned values using the IF command.

=IF(M16>L16,Data!B7)

Sorry if this is confusing or not well explained

]]>Projected completion date > Actual Date it would return one value, if it was < it would return a different value, and if it was >but -5 would return yet another value. The values are just text

In this example I am returning a value of "Yes" but I am not sure how or if I can accomplish the other 2 returned values using the IF command.

=IF(M16>L16,Data!B7)

Sorry if this is confusing or not well explained

https://i.imgur.com/JXWwXPZ.png

So column E and F give percentages of long and short. Column G is the ratio.

What im looking for is if column F(shorts) is greater than column E(longs) then the ratio will a minus.

]]>So column E and F give percentages of long and short. Column G is the ratio.

What im looking for is if column F(shorts) is greater than column E(longs) then the ratio will a minus.

Good afternoon all, I'm new to this forum and hoping some one can help.

I am having a specific problem and am very much hoping that you can help. I have added the simplified problem below; I am trying to get a cell on a sheet to return the name of the participant who won a given race (or came 2nd, 3rd, 4th etc) when the results are on one page, the participant name on a second and the formula on a third. I have included the formula that I have used with google forms (which uses formulas in a similar way to excel), however, the result always comes back as N/A if the first criteria is not met.

I hope that makes sense and hope you can offer a solution.

Sheet called Results

A B C

1 Team A Team B

2 Position 1 2

Sheet called Participants

A B C

1 Team A Team B

2 Participant Matt Laura

Sheet called Winners

A

1 1st Place

2 =if(‘Results’!B2=1,’Participants!B2,if((‘Results’!C2=1,’Participants!C2))

I very much hope some one can either see where i am going wrong or offer another suggestion.

Thanks

TJ

]]>I am having a specific problem and am very much hoping that you can help. I have added the simplified problem below; I am trying to get a cell on a sheet to return the name of the participant who won a given race (or came 2nd, 3rd, 4th etc) when the results are on one page, the participant name on a second and the formula on a third. I have included the formula that I have used with google forms (which uses formulas in a similar way to excel), however, the result always comes back as N/A if the first criteria is not met.

I hope that makes sense and hope you can offer a solution.

Sheet called Results

A B C

1 Team A Team B

2 Position 1 2

Sheet called Participants

A B C

1 Team A Team B

2 Participant Matt Laura

Sheet called Winners

A

1 1st Place

2 =if(‘Results’!B2=1,’Participants!B2,if((‘Results’!C2=1,’Participants!C2))

I very much hope some one can either see where i am going wrong or offer another suggestion.

Thanks

TJ

Hi

I have this spreadsheet that downloads stockprices from Google. ( http://investexcel.net/free-intraday-stock-data-excel/ ) The name of the stock (ticker) get inserted into parameters, and the prices and timestamps get automatically inserted into the sheet called*Data*

I have made a new sheet*15 good day* where I want to process this data.

Normaly the stock price is recorded for every minute, but that is not the case with all the stocks (meaning it jumps over some minutes). This means the cells of the prices and timestamps changes row position for every stock) (try typing in IBM and press "get data from google" in the parameter sheet, then try ASTC as ticker, and you will see in the*Data* sheet what I mean)

The only thing I'm certain that is present every time a new stock is inserted, is the letter*a* in front of the unix code for the day, and since I will only import data for 3 days each time, I can find the first, second and third **a** in column A on the *Data sheet*, and then work my way from there to get the data which I want, since the formulas are not now depended on the data to be on the same row for every time the stock change.

What I'm trying to do, is to combine a*Look up a value and return cell * **=INDEX(Data!B8:Data!B25;MATCH("a";Data!A8:Data!A25;0)+0;1)**

https://www.extendoffice.com/documen...-or-above.html

and then replace the**a** in the formula, with a *Vlookup Find The First, 2nd Or Nth Match Value In Excel*

**=INDEX($A$8:$A$1000;SMALL(IF("a"=$A$8:$A$1000;ROW($A$8:$A$1000)-ROW($A$8)+1);1)) **

https://www.extendoffice.com/documen...2nd-match.html

To get a formula that finds the first**a** in coloum A in *Data sheet* (from row 8 and down),the formula then gives me the value to the right cell.

I then want to be able to make changes to this new formula to find:

-The second and third**a** in coloumn A, to get data for day 2 and 3.

-The lowest/highest value within the first and last hour,

-The same data only with the volume

In the picture below, a lot of rows are hidden to show where the**a**'s are in front of the unix codes.

data.jpg

IF there is a better formula to solve this, then I'm all up for it, I'm not that good with formulas in excel, so it's a lot of cut and paste from different site that pops up from google :rolleyes:

Thanks a lot for a great forum btw :)

I have this spreadsheet that downloads stockprices from Google. ( http://investexcel.net/free-intraday-stock-data-excel/ ) The name of the stock (ticker) get inserted into parameters, and the prices and timestamps get automatically inserted into the sheet called

I have made a new sheet

Normaly the stock price is recorded for every minute, but that is not the case with all the stocks (meaning it jumps over some minutes). This means the cells of the prices and timestamps changes row position for every stock) (try typing in IBM and press "get data from google" in the parameter sheet, then try ASTC as ticker, and you will see in the

The only thing I'm certain that is present every time a new stock is inserted, is the letter

What I'm trying to do, is to combine a

https://www.extendoffice.com/documen...-or-above.html

and then replace the

https://www.extendoffice.com/documen...2nd-match.html

To get a formula that finds the first

I then want to be able to make changes to this new formula to find:

-The second and third

-The lowest/highest value within the first and last hour,

-The same data only with the volume

In the picture below, a lot of rows are hidden to show where the

data.jpg

IF there is a better formula to solve this, then I'm all up for it, I'm not that good with formulas in excel, so it's a lot of cut and paste from different site that pops up from google :rolleyes:

Thanks a lot for a great forum btw :)

Hello Experts,

I am trying to create a dashboard in excel.

I want to display all the data on my sheet first, so,

I am getting an error of #NA as there is no data available.

it's okay but

i want " " instead of #NA.

For this, I used iferror() of IFNA() but not working, please check

find the attached sheet

I am trying to create a dashboard in excel.

I want to display all the data on my sheet first, so,

I am getting an error of #NA as there is no data available.

it's okay but

i want " " instead of #NA.

For this, I used iferror() of IFNA() but not working, please check

find the attached sheet

Hiya

I have a 'table' of data (not an actual table, though this might be the right direction) that has 'groups' of 4 cells; x4 'groups' wide. And then continues on for many rows worth.

I want to reference the data into 4 new columns, (with clearly 4x as many rows).

Heres an example: (numbers and text are in practice all numbers, but this clearer identifies the data groups)

**0 CAT DOG FISH 1 SQUARE CIRCLE TRIANGLE 2 CUP SPOON KNIFE 3 UP DOWN LEFT**

4 HAT GLOVES COAT 5 PEN PENCIL ERASER 6 CAR TRUCK BUS 7 HARD SOFT SQUISHY

8 MAN LADY KID 9 PC MAC PLAYSTATION 10 HORSE COW SHEEP 11 PLASTIC METAL WOOD

Desired new list:

**0 CAT DOG FISH**

1 SQUARE CIRCLE TRIANGLE

2 CUP SPOON KNIFE

3 UP DOWN LEFT

4 HAT GLOVES COAT

5 PEN PENCIL ERASER

6 CAR TRUCK BUS

7 HARD SOFT SQUISHY

8 MAN LADY KID

9 PC MAC PLAYSTATION

10 HORSE COW SHEEP

11 PLASTIC METAL WOOD

and so on...

Attached is the actual sheet, and my start on the right hand side is where I have manually referenced the cells to indicate what I need to achieve. Ignore the RAW sheet, this is where I paste the data from my Modem.

The sheet probably makes a lot more sense than my example above; to be fair... :)

Thanks in advance.

I know there must be some formula that will do this, somehow referencing cell by row and col number or similar, but the formulas I have seen for collapsing columns into rows etc don't account for my groups of 4 across.

Any help greatly appreciated! You guys have been magic in the past!

Paul

I have a 'table' of data (not an actual table, though this might be the right direction) that has 'groups' of 4 cells; x4 'groups' wide. And then continues on for many rows worth.

I want to reference the data into 4 new columns, (with clearly 4x as many rows).

Heres an example: (numbers and text are in practice all numbers, but this clearer identifies the data groups)

4 HAT GLOVES COAT 5 PEN PENCIL ERASER 6 CAR TRUCK BUS 7 HARD SOFT SQUISHY

8 MAN LADY KID 9 PC MAC PLAYSTATION 10 HORSE COW SHEEP 11 PLASTIC METAL WOOD

Desired new list:

1 SQUARE CIRCLE TRIANGLE

2 CUP SPOON KNIFE

3 UP DOWN LEFT

4 HAT GLOVES COAT

5 PEN PENCIL ERASER

6 CAR TRUCK BUS

7 HARD SOFT SQUISHY

8 MAN LADY KID

9 PC MAC PLAYSTATION

10 HORSE COW SHEEP

11 PLASTIC METAL WOOD

and so on...

Attached is the actual sheet, and my start on the right hand side is where I have manually referenced the cells to indicate what I need to achieve. Ignore the RAW sheet, this is where I paste the data from my Modem.

The sheet probably makes a lot more sense than my example above; to be fair... :)

Thanks in advance.

I know there must be some formula that will do this, somehow referencing cell by row and col number or similar, but the formulas I have seen for collapsing columns into rows etc don't account for my groups of 4 across.

Any help greatly appreciated! You guys have been magic in the past!

Paul

I need to calculate an if function formula, (at least I think its an if function).

I have multiple staff, everyone is at a different pay rate.. If I use a drop down menu to choose the staff member that's in B2, and the service in C2, I want I2 to calculate the pay for that selected service.

I am currently using a Vlookup formula, but everyone was at the same rate of pay. Since that is no longer the case, I need to change the formula.

I have attached a sample of my spreadsheet in case it will help.

Thanks in advance.

I have multiple staff, everyone is at a different pay rate.. If I use a drop down menu to choose the staff member that's in B2, and the service in C2, I want I2 to calculate the pay for that selected service.

I am currently using a Vlookup formula, but everyone was at the same rate of pay. Since that is no longer the case, I need to change the formula.

I have attached a sample of my spreadsheet in case it will help.

Thanks in advance.

I'm very new to excel and making a spreadsheet for my own data. I made a macros button that will insert a new row always as row 4 which is great so i can have new data always at the top.

Only problem which you can see in the picture is the new row has the color formatting of row 3 when i want it to be row 4 so it all looks the same.

https://i.imgur.com/IzUnshN.png

Here is what i have in macros:

Private Sub CommandButton1_Click()

Sheets("AUD").Range("A4").Select

ActiveCell.EntireRow.Insert Shift:=x1Down

Sheets("AUD").Range("A4:S4").Select

Selection.Borders.Weight = xlThin

Sheets("AUD").Range("d4").Select

ActiveCell.Formula = "=b4+c4"

Sheets("AUD").Range("e4").Select

ActiveCell.Formula = "=B4/D4"

Sheets("AUD").Range("f4").Select

ActiveCell.Formula = "=C4/D4"

Sheets("AUD").Range("g4").Select

ActiveCell.Formula = "=B4/C4"

Sheets("AUD").Range("h4").Select

ActiveCell.Formula = "=B4-C4"

Sheets("AUD").Range("l4").Select

ActiveCell.Formula = "=J4+K4"

Sheets("AUD").Range("m4").Select

ActiveCell.Formula = "=J4/L4"

Sheets("AUD").Range("n4").Select

ActiveCell.Formula = "=K4/L4"

Sheets("AUD").Range("o4").Select

ActiveCell.Formula = "=J4/K4"

Sheets("AUD").Range("p4").Select

ActiveCell.Formula = "=J4-K4"

Sheets("AUD").Range("r4").Select

ActiveCell.Formula = "=SUM(ABS(H4),ABS(P4))"

Sheets("AUD").Range("s4").Select

ActiveCell.Formula = "=G4+O4"

End Sub

]]>Only problem which you can see in the picture is the new row has the color formatting of row 3 when i want it to be row 4 so it all looks the same.

https://i.imgur.com/IzUnshN.png

Here is what i have in macros:

Private Sub CommandButton1_Click()

Sheets("AUD").Range("A4").Select

ActiveCell.EntireRow.Insert Shift:=x1Down

Sheets("AUD").Range("A4:S4").Select

Selection.Borders.Weight = xlThin

Sheets("AUD").Range("d4").Select

ActiveCell.Formula = "=b4+c4"

Sheets("AUD").Range("e4").Select

ActiveCell.Formula = "=B4/D4"

Sheets("AUD").Range("f4").Select

ActiveCell.Formula = "=C4/D4"

Sheets("AUD").Range("g4").Select

ActiveCell.Formula = "=B4/C4"

Sheets("AUD").Range("h4").Select

ActiveCell.Formula = "=B4-C4"

Sheets("AUD").Range("l4").Select

ActiveCell.Formula = "=J4+K4"

Sheets("AUD").Range("m4").Select

ActiveCell.Formula = "=J4/L4"

Sheets("AUD").Range("n4").Select

ActiveCell.Formula = "=K4/L4"

Sheets("AUD").Range("o4").Select

ActiveCell.Formula = "=J4/K4"

Sheets("AUD").Range("p4").Select

ActiveCell.Formula = "=J4-K4"

Sheets("AUD").Range("r4").Select

ActiveCell.Formula = "=SUM(ABS(H4),ABS(P4))"

Sheets("AUD").Range("s4").Select

ActiveCell.Formula = "=G4+O4"

End Sub

Hello Forum,

Is there any way to use excel aggregate function on a countif formula? I am counting text words within a cell which can have multiple counts but I want to be to be able to filter it with the aggregate function

]]>Is there any way to use excel aggregate function on a countif formula? I am counting text words within a cell which can have multiple counts but I want to be to be able to filter it with the aggregate function

=IF((A2/B2>=0.6)*(A2/B2<=0.8),A2/B2,0)

This is my formula, if A2/B2 is between 0.6 and 0.8, display the result of the division otherwise list 0. However, it doesn't take into consideration that B2 can be 0 and in that case I will receive DVD/0 error message but if I get the error message, I want to display 0 as well. I know I need ISERROR or IFERROR but I am not sure what the syntax is. Can you please help?

]]>This is my formula, if A2/B2 is between 0.6 and 0.8, display the result of the division otherwise list 0. However, it doesn't take into consideration that B2 can be 0 and in that case I will receive DVD/0 error message but if I get the error message, I want to display 0 as well. I know I need ISERROR or IFERROR but I am not sure what the syntax is. Can you please help?

Hi im looking for a formula to sum two numbers together. Sometimes one column will have negative number but the total has to always be positive.

Heres a photo:

https://i.imgur.com/5FnD4UV.png

I need the two net positions to equal a positive number, so its as if the minus is not in a column

]]>Heres a photo:

https://i.imgur.com/5FnD4UV.png

I need the two net positions to equal a positive number, so its as if the minus is not in a column

Hello,

First time post. If I am missing information, let me know and I will add.

I have created a schedule builder for two salary and four hourly employees. I am integrating data calculations from one of my weekly reports to help speed things along.

I want to be able to calculate the total labor rates per day for all employees working that day including the salary. I am having trouble creating the formula to calculate the daily labor rates for the hourly employees once overtime rates come into play. I have been using nested IF formulas to calculate up to 40 hours and over 40 hours. The part of the formula I am stuck on is when there are both regular and overtime hours for an employee on a single day.

Attached is a test version of my workbook. I have blanked out unrelevant data calculations. You can see the formulas I have been developing on row 34 columns B-O. Column J is where I put the day with both regular and overtime rates. The current formula in column J is my attempt to calculate the overtime rate minus the regular rate, but I am stumped on how to add the regular hours back into the rate.

Thank you in advance for any help.

First time post. If I am missing information, let me know and I will add.

I have created a schedule builder for two salary and four hourly employees. I am integrating data calculations from one of my weekly reports to help speed things along.

I want to be able to calculate the total labor rates per day for all employees working that day including the salary. I am having trouble creating the formula to calculate the daily labor rates for the hourly employees once overtime rates come into play. I have been using nested IF formulas to calculate up to 40 hours and over 40 hours. The part of the formula I am stuck on is when there are both regular and overtime hours for an employee on a single day.

Attached is a test version of my workbook. I have blanked out unrelevant data calculations. You can see the formulas I have been developing on row 34 columns B-O. Column J is where I put the day with both regular and overtime rates. The current formula in column J is my attempt to calculate the overtime rate minus the regular rate, but I am stumped on how to add the regular hours back into the rate.

Thank you in advance for any help.

Hello,

i am trying to create an if statement that looks at multiple cells and returns set verbiage based on the results.

Here is my formula:

=IF(AND(E375<2,F375<2,F381<5),"Pass", IF(AND(E375>1,F375>1,F381>4),"Fail"))

So when the condition is met, "Pass" is generated but when it does not, "false" generates instead of "fail"

anybody have any ideas?

]]>i am trying to create an if statement that looks at multiple cells and returns set verbiage based on the results.

Here is my formula:

=IF(AND(E375<2,F375<2,F381<5),"Pass", IF(AND(E375>1,F375>1,F381>4),"Fail"))

So when the condition is met, "Pass" is generated but when it does not, "false" generates instead of "fail"

anybody have any ideas?

Good afternoon all,

I have a bit of an odd formula need. In the attached sample, I have sku counts for 4 different categories. What I am hoping to do is utilize a formula to create a dynamic list of sequential numbers, in one column, that start over at 1 for each criteria. I utilized a row formula to get through the first category without issue. I am hoping to get assistance with adding in the other three, which I have noted as "desired" results. Hopefully this makes sense and thanks in advance,

I have a bit of an odd formula need. In the attached sample, I have sku counts for 4 different categories. What I am hoping to do is utilize a formula to create a dynamic list of sequential numbers, in one column, that start over at 1 for each criteria. I utilized a row formula to get through the first category without issue. I am hoping to get assistance with adding in the other three, which I have noted as "desired" results. Hopefully this makes sense and thanks in advance,

Hi, I know how to get the tab name of the current sheet into a cell, I use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255).

How do I get the tab name of a different sheet, say Sheet5 ???

I need to use it in a formula !

Thx Trebor

]]>How do I get the tab name of a different sheet, say Sheet5 ???

I need to use it in a formula !

Thx Trebor