hi friends!!!! hope you guys can help me with this!!! the IF funtion has to buy inventory if it goes -75 the inventory is 100 and the buy order is blank....

.............START JA FE MA AP

p sales........... 31 34 25 30

p.invtory 100 69 35 10 -20

p buy order=IF????????

I DONT even know if an IF funtion works to this situation i try every way possible im feeling kind of dumb this is for a assingment if i pass the quizz with only this one wrong i will never will know the solution because in the end they dont reveal the solutions i need to know i hate that im not able to solve this...

i watched hours of youtub videos and im still clueless about this one ....

regards

]]>.............START JA FE MA AP

p sales........... 31 34 25 30

p.invtory 100 69 35 10 -20

p buy order=IF????????

I DONT even know if an IF funtion works to this situation i try every way possible im feeling kind of dumb this is for a assingment if i pass the quizz with only this one wrong i will never will know the solution because in the end they dont reveal the solutions i need to know i hate that im not able to solve this...

i watched hours of youtub videos and im still clueless about this one ....

regards

I apologize if this was covered elsewhere- I tried a search first and found similar threads, but nothing that helped. I have a report that lists deficiencies- each deficiency has it's own ID number, which I put in column A (specifically, A3:A192). In column I (I3:I192) is the inspection date- the date the deficiency was first noted. In J (J3:J192) is the completion date- when the deficiency was corrected. We have 30 days to correct deficiencies and close them out. So, I need a formula that tells me how many open deficiencies that I have that are over 30 days. There are a total of 15 open deficiencies. This should be simple, but I'm stymied. I tried a couple of formulas to show how many were over 30 days.

First, I tried keeping it simple with =COUNTIF(I3:I192,">" & TODAY()-30) but that rendered 0. I also tried a variation of replacing "TODAY()" with cell B195 where I also had a TODAY() formula. Same result: zero.

Next, I tried getting fancier with =SUMPRODUCT(--ISNUMBER(A3:A192),--(I3:I192>B195-30),--(J3:J192="")). That gave me 15. Problem is, 15 is the total number of open deficiencies that I have, under and over 30 days.

Any suggestions?

]]>First, I tried keeping it simple with =COUNTIF(I3:I192,">" & TODAY()-30) but that rendered 0. I also tried a variation of replacing "TODAY()" with cell B195 where I also had a TODAY() formula. Same result: zero.

Next, I tried getting fancier with =SUMPRODUCT(--ISNUMBER(A3:A192),--(I3:I192>B195-30),--(J3:J192="")). That gave me 15. Problem is, 15 is the total number of open deficiencies that I have, under and over 30 days.

Any suggestions?

I have a list of "Vendors" & a list of "Customers" with some companies being in both categories. I've made a "Vendors" summary page and a "Customers" summary page, but I'd like to combine the two into a third "Combined" summary page. I've already worked-out (with great help from the forum) how to consolidate the two lists into one, but I still need help in combining the data.

The Vendors have "(V)" at the end of their names and the customers have "(C)".

Maybe my brain is turning to mush, but I can't even figure-out where to start formula-speaking. :confused:

Any hint, suggestions, or prods would be greatly appreciated.

I've attached a reduced size file and an image to help visually:

Combine Data.jpg

The Vendors have "(V)" at the end of their names and the customers have "(C)".

Maybe my brain is turning to mush, but I can't even figure-out where to start formula-speaking. :confused:

Any hint, suggestions, or prods would be greatly appreciated.

I've attached a reduced size file and an image to help visually:

Combine Data.jpg

I am trying to enter correct formula for T.I.L. hours on Timesheet that contains see below. Please find Excel worksheet attached. Please refer to green highlights in the attachment which includes for your information and it explains how it works.

"Normal Operating Hours" 8.00am to 6.00pm Monday to Friday.

"Normal Start and Finish Times" 9.00am to 5.06pm

"Ordinary hours" 38 hours per week

"Length of an Ordinatory working day" 7.36 hours or 7.6 hours in decimal plus an unpaid 30 minute meal break

"Meal and Rest break" Employees will not be required to work more than 5 hours without an unpaid meal beak of at least 30 minutes duration

1.5 hours of TIL for each of the first 2 hours of overtime worked then 2 hours of TIL for every overtime hour worked (Monday - Saturday)

For time worked on Sunday 2 hours of TIL for every hour worked

Thanks.

"Normal Operating Hours" 8.00am to 6.00pm Monday to Friday.

"Normal Start and Finish Times" 9.00am to 5.06pm

"Ordinary hours" 38 hours per week

"Length of an Ordinatory working day" 7.36 hours or 7.6 hours in decimal plus an unpaid 30 minute meal break

"Meal and Rest break" Employees will not be required to work more than 5 hours without an unpaid meal beak of at least 30 minutes duration

1.5 hours of TIL for each of the first 2 hours of overtime worked then 2 hours of TIL for every overtime hour worked (Monday - Saturday)

For time worked on Sunday 2 hours of TIL for every hour worked

Thanks.

Hi All,

Am having issue to compare data at work, usually I'll use filter to look at it line by line (around 3000)...and usually takes me days to validate data.

Heard there are many function in excel can spot the different, would like to ask the expert for:

1. Excel will tell Example B and Example D contains data inconsistency of the Date in Column B

Example A 1/1/1900

Example A 1/1/1900

Example B 1/1/1900

Example B 1/1/1900

Example B 1/2/1900

Example C 1/1/1900

Example D 1/3/1900

Example D 1/3/1900

Example D 1/2/1900

Example D 1/1/1900

Example D 1/4/1900

Thanks

Alan

]]>Am having issue to compare data at work, usually I'll use filter to look at it line by line (around 3000)...and usually takes me days to validate data.

Heard there are many function in excel can spot the different, would like to ask the expert for:

1. Excel will tell Example B and Example D contains data inconsistency of the Date in Column B

Example A 1/1/1900

Example A 1/1/1900

Example B 1/1/1900

Example B 1/1/1900

Example B 1/2/1900

Example C 1/1/1900

Example D 1/3/1900

Example D 1/3/1900

Example D 1/2/1900

Example D 1/1/1900

Example D 1/4/1900

Thanks

Alan

I have a spreadsheet that will contain 3 dates. The first date that can be entered will be a date of birth, a few columns later there will be a date sample was taken followed by date the sample was analyzed. I would like to ensure that the DOB meets certain criteria. DOB must not make someone older than 90 years old, DOB should not be for someone who is less than 60 days old (no newborns). For the date sample was taken, oftentimes the end user will accidentally put in the dob instead of the date of the test/sample, so I want it to always be greater than (ideally 60 days more than) the birth date, but not a future date. For the date analyzed, it cannot be before the date the sample was taken, cannot be before the person was born, cannot be in the future, and I would like it to flag (but allow the entry) if the date analyzed is greater than 14 days from the date the sample was taken?

examples: Assuming Today is 01/20/2017, then DOB cannot be after 11/20/2016 (or whatever 60 days is), the date of the sample can be today's date (1/20/2017, and the date analyzed can also be today's date (date analyzed may equal date of sample, but date analyzed cannot be before date sampled) If date sampled = 12/20/2016, and user enters 01/20/2017 for date analyzed, there should be an alert that would ask if they were sure they waited 30 days (doesn't have to specify the difference between the dates, it can just say 'did you really not analyze this within 2 weeks?) to analyze a sample? (but, if they really did screw up like this, then it allows the date to be entered). What would the data validation formulas look like for these instances??

]]>examples: Assuming Today is 01/20/2017, then DOB cannot be after 11/20/2016 (or whatever 60 days is), the date of the sample can be today's date (1/20/2017, and the date analyzed can also be today's date (date analyzed may equal date of sample, but date analyzed cannot be before date sampled) If date sampled = 12/20/2016, and user enters 01/20/2017 for date analyzed, there should be an alert that would ask if they were sure they waited 30 days (doesn't have to specify the difference between the dates, it can just say 'did you really not analyze this within 2 weeks?) to analyze a sample? (but, if they really did screw up like this, then it allows the date to be entered). What would the data validation formulas look like for these instances??

Hi there,

I am trying to learn VBA / Macro's but am finding it difficult to apply the following to a large data spreadsheet.

I would be grateful for your help?

Please see attached for a sample of the data.

I have data in columns E, O, S and U which changes. What I would like to do automatically is when the value changes, insert a new blank row to separate them.

For example, a blank row to be inserted under row 4, 6, 9, 10, 235 etc. There is approx. 12,000 rows and there is no consistency in the number of rows before a unique value appears.

Is this possible? It can be based on 1 column as far as I am aware.

Many thanks for your help.

Regards

N

I am trying to learn VBA / Macro's but am finding it difficult to apply the following to a large data spreadsheet.

I would be grateful for your help?

Please see attached for a sample of the data.

I have data in columns E, O, S and U which changes. What I would like to do automatically is when the value changes, insert a new blank row to separate them.

For example, a blank row to be inserted under row 4, 6, 9, 10, 235 etc. There is approx. 12,000 rows and there is no consistency in the number of rows before a unique value appears.

Is this possible? It can be based on 1 column as far as I am aware.

Many thanks for your help.

Regards

N

Hello:

Please refer to attached file.

I need formula in D8 which will give the date close to 14th but need to make sure that it is weekdays.

In the example:

If I select month 1 (Jan) in cell A1, the 14th is Sat, answer i need in cell D8 will be 2/13/2017

If I select month 2 (Feb) in cell A1, the 14th is Tue, answer i need in cell D8 will be 2/14/2017

If I select month 9 (Sep) in cell A1, the 14th is Tue, answer i need in cell D8 will be 9/14/2017

Let me know if you have any questions.

Thanks.

Riz

Please refer to attached file.

I need formula in D8 which will give the date close to 14th but need to make sure that it is weekdays.

In the example:

If I select month 1 (Jan) in cell A1, the 14th is Sat, answer i need in cell D8 will be 2/13/2017

If I select month 2 (Feb) in cell A1, the 14th is Tue, answer i need in cell D8 will be 2/14/2017

If I select month 9 (Sep) in cell A1, the 14th is Tue, answer i need in cell D8 will be 9/14/2017

Let me know if you have any questions.

Thanks.

Riz

Hello Everyone, sorry if this was answered already but I could not locate it.

Assuming that a,b,c,d are people, 1-9 are dates, "1" is when I have them present, "h" when they are a home. I need to know how many times each one in a given period has been at home.

I need the formula to count how many times in a table(row) a value is changing from "1" to "h". If "h" is in the next cell it should be considered as one time.

0 1 2 3 4 5 6 7 8**Should look like**

a h 1 1 1 h h h 1**2**

b h 1 h 1 1 h h 1**3**

c 1 1 1 1 h 1 1 1**1**

d h 1 1 1 1 h h 1**2**

Thanks in advance

]]>Assuming that a,b,c,d are people, 1-9 are dates, "1" is when I have them present, "h" when they are a home. I need to know how many times each one in a given period has been at home.

I need the formula to count how many times in a table(row) a value is changing from "1" to "h". If "h" is in the next cell it should be considered as one time.

0 1 2 3 4 5 6 7 8

a h 1 1 1 h h h 1

b h 1 h 1 1 h h 1

c 1 1 1 1 h 1 1 1

d h 1 1 1 1 h h 1

Thanks in advance

I've got a column "I" where I have pulled all the sheet names in the workbook. I am now trying to alphabetize the results in column "K". I have a formula that works beautifully, but it lists all the blank cells at the bottom of column "I" at the top of column "K". I need either a way to change the formula in column I to retrieve the sheet names in alphabetical order, or have the formula in column "K" sort while removing blanks.

Any help or suggestions would be greatly appreciated.

I've attached a reduced version of the file below.

Here is the formula in column "I":

Here is the formula in column "K":

Sort nio blanks.jpg

Any help or suggestions would be greatly appreciated.

I've attached a reduced version of the file below.

Here is the formula in column "I":

Formula:

=IFERROR(INDEX(MID(TABS,FIND("]",TABS)+1,255),ROWS(B$4:B4)),"")

Here is the formula in column "K":

Formula:

=INDEX(I$5:I$90,MATCH(ROWS(K$5:K5),$J$5:$J$90,0))

Sort nio blanks.jpg

Hello:

Please refer to attached file.

I am now trying to learn the beauty of Pivot Table in Excel.

I have data as shown in Sheet1 and have created Pivot Table in Sheet3 to see the amount for 2014,2015 and 2016 for Jan and Feb.

I need to know how to add Variance in column E so that i can see increase/decrease %.

Please let me know if you have any questions.

Thanks.

Riz

Let me know if you have any questions.

Thanks.

Please refer to attached file.

I am now trying to learn the beauty of Pivot Table in Excel.

I have data as shown in Sheet1 and have created Pivot Table in Sheet3 to see the amount for 2014,2015 and 2016 for Jan and Feb.

I need to know how to add Variance in column E so that i can see increase/decrease %.

Please let me know if you have any questions.

Thanks.

Riz

Let me know if you have any questions.

Thanks.

Hello all,

I have a list of user id numbers with column for project codes. I need a column of user numbers in order of most frequent to least frequent users and the first and second most common project code for each individual user. I have about 1000 users and 200 project codes.

Users project ID

2772014193313 45

5707019721787 90.1

2728014156631 28

9944021574683 23

7571021559731 45

0062014161058 45

2752014152529 92

2765014231250 92

2472021646956 90.1

2735014201012 21

0084014305453 44

0071014146767 45

5712021271901 23

3637021591357 22

2792014162880 45

0072014188672 92

5709020308585 92

0057014144069 90.1

0044014181513 86

2774014199691 86

2729014163395 99

3609021582019 99

0075014189667 52

2732014233685 55

1881014152155 64

5707020025648 66

2759014170424 64

3613021554476 90.2

5707020015943 90.3

0051014211164 45

3616021569353 44

2788014223373 67

1880014157040 66

3623021547947 65

0087014174006 46

2787014172910 58

2767014367857 55

Thank you for any help you can provide.

]]>I have a list of user id numbers with column for project codes. I need a column of user numbers in order of most frequent to least frequent users and the first and second most common project code for each individual user. I have about 1000 users and 200 project codes.

Users project ID

2772014193313 45

5707019721787 90.1

2728014156631 28

9944021574683 23

7571021559731 45

0062014161058 45

2752014152529 92

2765014231250 92

2472021646956 90.1

2735014201012 21

0084014305453 44

0071014146767 45

5712021271901 23

3637021591357 22

2792014162880 45

0072014188672 92

5709020308585 92

0057014144069 90.1

0044014181513 86

2774014199691 86

2729014163395 99

3609021582019 99

0075014189667 52

2732014233685 55

1881014152155 64

5707020025648 66

2759014170424 64

3613021554476 90.2

5707020015943 90.3

0051014211164 45

3616021569353 44

2788014223373 67

1880014157040 66

3623021547947 65

0087014174006 46

2787014172910 58

2767014367857 55

Thank you for any help you can provide.

Hi,

I'm trying to create a report that will allow me to select the location from a List, And then the WEEK, and then it populates the fields based on some formula. I know it has to do with Indexing, i believe, but I haven't been able to make it work yet.

I want Sum(TotalRev), Sum(profit), Sum(goals) to be populated by selecting The store, then Week. It should then see the data for that time period, sum it, and populate the cells. Weeks need to be set Monday through Sunday. Is that possible?

I've attached the sheet, and the cells that should contain the lists are highighted in yellow, the cells that should populate, in green.

Thank you very much for your help.

I'm trying to create a report that will allow me to select the location from a List, And then the WEEK, and then it populates the fields based on some formula. I know it has to do with Indexing, i believe, but I haven't been able to make it work yet.

I want Sum(TotalRev), Sum(profit), Sum(goals) to be populated by selecting The store, then Week. It should then see the data for that time period, sum it, and populate the cells. Weeks need to be set Monday through Sunday. Is that possible?

I've attached the sheet, and the cells that should contain the lists are highighted in yellow, the cells that should populate, in green.

Thank you very much for your help.

I can't seem to make this work. How do I show someone the spreadsheet for help?

this is in cell B1 =VLOOKUP($A$3,'MB2'!$B$3:$J$42,1,FALSE)

C3 is a drop down box

B1 is this result #N/A

SELECT Variety Trait Type

PM12467UN 2162 CRX Soybeans 2.1 2,691 52 UNT

2162 CRX Soybeans 2.1 2,691 52 SO

]]>this is in cell B1 =VLOOKUP($A$3,'MB2'!$B$3:$J$42,1,FALSE)

C3 is a drop down box

B1 is this result #N/A

SELECT Variety Trait Type

PM12467UN 2162 CRX Soybeans 2.1 2,691 52 UNT

2162 CRX Soybeans 2.1 2,691 52 SO

I can't seem to make this work. How do I show someone the spreadsheet for help?

]]>