I want to find the lowest value in a column. I know to use MIN(A1:A100).

But the A1 column I want to use is partially populated; it has non-zero from A1 to A?, the rest of the cells in the column

are zero

So: A1 to A20 are non-zero; A21 to A100 are zero.

I wanted to use LOOKUP to return the cell in the column with the last non-zero value,

then use MIN to find the lowest value in the populated cells:

MIN(A1:last populated cell from LOOKUP)

But Excel keeps telling me =MIN(A1:LOOKUP(2,1/(A1:A100<>0),ROW(A1:A100))) is not a formula

"There's a problem with this formula.

Not trying to type a formula?" etc

Please tell me what I'm doing wrong.

I'm a newbie, so I'm here asking for your help.

Thank you for your help,

Tom

]]>But the A1 column I want to use is partially populated; it has non-zero from A1 to A?, the rest of the cells in the column

are zero

So: A1 to A20 are non-zero; A21 to A100 are zero.

I wanted to use LOOKUP to return the cell in the column with the last non-zero value,

then use MIN to find the lowest value in the populated cells:

MIN(A1:last populated cell from LOOKUP)

But Excel keeps telling me =MIN(A1:LOOKUP(2,1/(A1:A100<>0),ROW(A1:A100))) is not a formula

"There's a problem with this formula.

Not trying to type a formula?" etc

Please tell me what I'm doing wrong.

I'm a newbie, so I'm here asking for your help.

Thank you for your help,

Tom

Hi everyone. I have a school time table (2 weeks) which I need to be able to extract the information for certain faculties from to create a smaller worksheet that has all the classes for that department. ie. be able to pull all the maths classes from the entire timetable, or all the english classes. Is there a way to say 'i need to just list/bold/colour/copy to a new spreadsheet all the classes that have *ma* or *en* in them?

I need to extract the information from the 'just classes' tab or the 'mastersheet' - just classes doesn't have teh additional ifnormation of the classrooms which we don't really need.

I need to extract the information from the 'just classes' tab or the 'mastersheet' - just classes doesn't have teh additional ifnormation of the classrooms which we don't really need.

Hi all, I'm sure this is pretty basic as far as Excel goes, but I'm pretty new.

I work in surveying and the way our projects are broken up is that there is a section which is composed of a number of samples. So one section may have 21 samples total and another may have 132. Each section has a unique name and I have a list in alphabetic order already (as well as numeric).

All I need to do is to check if the numbers are completely sequential in each section with no missing/repeating numbers.

I've attached an example sheet (I think) where I've simplified sections to letters and numbers as the samples. Here's the basic gist of it below as well. I've tried running an if statement to check that the current cell is the one above it +1, but I can't seem to figure out how to make the count reset for every new section. I have several hundred sections and thousands of samples, so it's not something I can manually go through and take out the flagged cells that are just going from one section to another

A 1

A 2

A 3

A 5 <--Flag this because not sequential

B 1

B 2

B 3

B 3 <--Flag this because it's a repeat

I work in surveying and the way our projects are broken up is that there is a section which is composed of a number of samples. So one section may have 21 samples total and another may have 132. Each section has a unique name and I have a list in alphabetic order already (as well as numeric).

All I need to do is to check if the numbers are completely sequential in each section with no missing/repeating numbers.

I've attached an example sheet (I think) where I've simplified sections to letters and numbers as the samples. Here's the basic gist of it below as well. I've tried running an if statement to check that the current cell is the one above it +1, but I can't seem to figure out how to make the count reset for every new section. I have several hundred sections and thousands of samples, so it's not something I can manually go through and take out the flagged cells that are just going from one section to another

A 1

A 2

A 3

A 5 <--Flag this because not sequential

B 1

B 2

B 3

B 3 <--Flag this because it's a repeat

I tried to put a Footer on a form I generated.

When I went to do that, my form data was lost and I ended up with a "Header - Footer" form.

I can re-create the form, but would like to know why this happened.

Thanks for your help.

Mike

When I went to do that, my form data was lost and I ended up with a "Header - Footer" form.

I can re-create the form, but would like to know why this happened.

Thanks for your help.

Mike

hello,

I am trying to make a table with value amount by dates

but I can't understand what is wrong with my function, and how to solve it?

I am trying to make a table with value amount by dates

but I can't understand what is wrong with my function, and how to solve it?

Hi Good day to everyone.

I need help in getting a formula of this problem. How can we solve for no of task required completed at a certain timeframe for the employees to pass at 100% to goal(lower is better). Im familiar but im not really good at algebra so now Im having a hard time solving this problem

Variables are

1.) No of tasks

2.) % to goal of the time employee took to complete the total no of task

3.) Goal at 100%

4.) No of tasks needed at at a certain timeframe in percentage(ex 90% of the time required) for the employee to finish

For example.

Employee 1 has 139% to goal at 20 tasks and his target goal is 100%(Lower the better). How many tasks does he needs to complete at 90% timeframe to reach 100%. I cant seem to find a solution online.

I need help in getting a formula of this problem. How can we solve for no of task required completed at a certain timeframe for the employees to pass at 100% to goal(lower is better). Im familiar but im not really good at algebra so now Im having a hard time solving this problem

Variables are

1.) No of tasks

2.) % to goal of the time employee took to complete the total no of task

3.) Goal at 100%

4.) No of tasks needed at at a certain timeframe in percentage(ex 90% of the time required) for the employee to finish

For example.

Employee 1 has 139% to goal at 20 tasks and his target goal is 100%(Lower the better). How many tasks does he needs to complete at 90% timeframe to reach 100%. I cant seem to find a solution online.

I'm trying to create a "tool" that when I cut and paste a list of numbers, similar to the ones below into a column with the IF formula, the TRUE result will yield the text beside it's corresponding number. I exceeded the NESTING limit of 64 by using, IF(A1=271817,"MIDNIGHT",IF(A1=100151,"TRITON GREY",IF(A1=100153,"TRITON BROWN, so and so forth until I had entered the entire list. Is there another way of achieving the desired results? Mind you, I'm a novice at this and may not pick up on tech short hand explanations.

Thanks,

Dave

271817 MIDNIGHT

100151 TRITON GREY

100153 TRITON BROWN

100155 STAR SILVER

100157 STAR COPPER

100159 SATURN BLUE

100161 SATURN WHITE

147837 ATHENA ROSE

147883 DESERT SAND

147983 REVERE SILVER

147990 ANTIQUE SILVER

147992 GOLDTONE

147994 MANDARIN

147995 EMERALDTONE

147998 REVERE SILVER

148001 FRANKLIN SILVER

148013 MERCURY

148015 ORION

148018 CHURCHILL BLUE

148026 GRAYTONE

148028 EARTHTONE

148029 REVERE SILVER

148030 CHURCHILL BLUE

148031 ROMAN

148035 MISTY ROSE ROSE

148038 SILVER CARNATION

148040 IVORY GOLD

148041 ANTIQUE BLUE

148043 PRIMROSE ROSE

148045 EBONYTONE

148046 SILVER TUSCANY

148049 WINEBERRY

148050 SIRRUS

148052 GALAXY

148054 LUMINA

148056 POLARIS

148060 CORETTA

148062 REVERE SILVER

148063 CHURCHILL BLUE GOING HOME

148068 HEIRLOOM PEWTER

148070 ROMAN

148073 SUNGLOW

148075 VINTAGE

148077 DESERT CHAMPAGNE

148078 ROSE BRONZE

148080 CORTEZ GOLD BIBLE

148081 HEIRLOOM PEWTER BIBLE

148083 MISTY BLUE

148086 CARNATION MIST ROSE

148088 IVORY MIST GOING HOME

148089 EARTHTONE

148090 SILVER EBONY-FC

148091 SILVER EBONY

212677 AUBURN

217298 GEMINI SILVER

217379 GEMINI WHITE/PINK

217383 GEMINI WHITE/WHITE/GOLD

217387 GEMINI COPPER

217389 GEMINI BLUE

221502 APOLLO SILVER

221503 APOLLO BLUE

221504 APOLLO COPPER

221505 APOLLO WHITE/PINK

221506 SPECTRA BLUE

221507 SPECTRA SILVER

223045 SPECTRA WHITE

234314 ARIES BLUE

234316 ARIES ORCHID

234318 ARIES SILVER

234320 ARIES COPPER

234322 ARIES WHITE

241683 APOLLO WHITE/WHITE

242475 HEIRLOOM PEWTER

242478 SAND LV

242739 SAND

244327 APOLLO FLAT BLACK

246026 OPAL-28

246306 SAND-32 LV

252011 CARNATION BLUSH

252031 IVORY LS/CAP

252121 OCEAN BLUE

252123 OCEAN BLUE-FC

256354 SPECTRA BLACK

257953 PISCES WHITE

257956 PISCES SILVER

257958 PISCES SAND

257960 SCORPIO BROWN

257962 SCORPIO GREY

258362 SPECTRA ORCHID

271815 AUBURN

271819 NEOPOLITAN BLUE

271821 REVERE SILVER LS4

272930 ORCHID

273920 CARNATION

278177 HUNTINGTON GREEN

278912 ARGO PAINTED ORCHID

278913 ARGO PAINTED BLACK

278920 ARGO PAINTED SILVER

278921 ARGO PAINTED SAND

279127 VIRGO BLUE

279131 VIRGO SILVER

279133 VIRGO WHITE/GOLD

279134 VIRGO WHITE/PINK

279135 VIRGO COPPER

]]>Thanks,

Dave

271817 MIDNIGHT

100151 TRITON GREY

100153 TRITON BROWN

100155 STAR SILVER

100157 STAR COPPER

100159 SATURN BLUE

100161 SATURN WHITE

147837 ATHENA ROSE

147883 DESERT SAND

147983 REVERE SILVER

147990 ANTIQUE SILVER

147992 GOLDTONE

147994 MANDARIN

147995 EMERALDTONE

147998 REVERE SILVER

148001 FRANKLIN SILVER

148013 MERCURY

148015 ORION

148018 CHURCHILL BLUE

148026 GRAYTONE

148028 EARTHTONE

148029 REVERE SILVER

148030 CHURCHILL BLUE

148031 ROMAN

148035 MISTY ROSE ROSE

148038 SILVER CARNATION

148040 IVORY GOLD

148041 ANTIQUE BLUE

148043 PRIMROSE ROSE

148045 EBONYTONE

148046 SILVER TUSCANY

148049 WINEBERRY

148050 SIRRUS

148052 GALAXY

148054 LUMINA

148056 POLARIS

148060 CORETTA

148062 REVERE SILVER

148063 CHURCHILL BLUE GOING HOME

148068 HEIRLOOM PEWTER

148070 ROMAN

148073 SUNGLOW

148075 VINTAGE

148077 DESERT CHAMPAGNE

148078 ROSE BRONZE

148080 CORTEZ GOLD BIBLE

148081 HEIRLOOM PEWTER BIBLE

148083 MISTY BLUE

148086 CARNATION MIST ROSE

148088 IVORY MIST GOING HOME

148089 EARTHTONE

148090 SILVER EBONY-FC

148091 SILVER EBONY

212677 AUBURN

217298 GEMINI SILVER

217379 GEMINI WHITE/PINK

217383 GEMINI WHITE/WHITE/GOLD

217387 GEMINI COPPER

217389 GEMINI BLUE

221502 APOLLO SILVER

221503 APOLLO BLUE

221504 APOLLO COPPER

221505 APOLLO WHITE/PINK

221506 SPECTRA BLUE

221507 SPECTRA SILVER

223045 SPECTRA WHITE

234314 ARIES BLUE

234316 ARIES ORCHID

234318 ARIES SILVER

234320 ARIES COPPER

234322 ARIES WHITE

241683 APOLLO WHITE/WHITE

242475 HEIRLOOM PEWTER

242478 SAND LV

242739 SAND

244327 APOLLO FLAT BLACK

246026 OPAL-28

246306 SAND-32 LV

252011 CARNATION BLUSH

252031 IVORY LS/CAP

252121 OCEAN BLUE

252123 OCEAN BLUE-FC

256354 SPECTRA BLACK

257953 PISCES WHITE

257956 PISCES SILVER

257958 PISCES SAND

257960 SCORPIO BROWN

257962 SCORPIO GREY

258362 SPECTRA ORCHID

271815 AUBURN

271819 NEOPOLITAN BLUE

271821 REVERE SILVER LS4

272930 ORCHID

273920 CARNATION

278177 HUNTINGTON GREEN

278912 ARGO PAINTED ORCHID

278913 ARGO PAINTED BLACK

278920 ARGO PAINTED SILVER

278921 ARGO PAINTED SAND

279127 VIRGO BLUE

279131 VIRGO SILVER

279133 VIRGO WHITE/GOLD

279134 VIRGO WHITE/PINK

279135 VIRGO COPPER

Hi all,

I’’m quite lost with this one.

I want to return Date in column F if E is not blank and keep all other cells empty for the same Serial_Nr. E.g. E4=“MDE” F4=“01/04/2021”. The problem is that every Serial_Nr has different amount of rows and I don’t know how to do the lookup.

I have tried =celll(format) but since the data are pasted, everything returns “general”.

Anyone knows how to deal with this?

Thanks

I’’m quite lost with this one.

I want to return Date in column F if E is not blank and keep all other cells empty for the same Serial_Nr. E.g. E4=“MDE” F4=“01/04/2021”. The problem is that every Serial_Nr has different amount of rows and I don’t know how to do the lookup.

I have tried =celll(format) but since the data are pasted, everything returns “general”.

Anyone knows how to deal with this?

Thanks

Hello,

I am struggling with a larger List of Products and I am supposed to make it even more detailed.

Currently I have the Sales for Product A, Product B and so on. Now I need a List where I attribute a Region to each of the Product names. Product A Asia, Product A Nord America...

One Line in the current list needs to become 4 new, I attached an example table that should make it clearer. Sorry for the

Thank you very much!

p.s. Is there an opportunity to donate to the forum (besides buying points?).

I am struggling with a larger List of Products and I am supposed to make it even more detailed.

Currently I have the Sales for Product A, Product B and so on. Now I need a List where I attribute a Region to each of the Product names. Product A Asia, Product A Nord America...

One Line in the current list needs to become 4 new, I attached an example table that should make it clearer. Sorry for the

Thank you very much!

p.s. Is there an opportunity to donate to the forum (besides buying points?).

Hello,

Let's assume that we have a certain value in A1 which is the positive integer "**x**".

We also have certain other positive integers in cells A20, A21, and A22 which are "**a**", "**b**" and "**c**" respectively. a, b and c are in ascending order.

I need to print the outcome of the comparison of**x** to these numbers, into the cell A30.

The rules I require are:

if c > x > b, A30 = a

if x > c, A30 = 0

if X < b, A30 = x

Would you help me with constructing the formula please?

Thanks in advance.

]]>Let's assume that we have a certain value in A1 which is the positive integer "

We also have certain other positive integers in cells A20, A21, and A22 which are "

I need to print the outcome of the comparison of

The rules I require are:

if c > x > b, A30 = a

if x > c, A30 = 0

if X < b, A30 = x

Would you help me with constructing the formula please?

Thanks in advance.

I have a sales history report that comes from webquery linked to an ERP system. I am trying to compile sales by person by month from a large data set. The attached spreadsheet is a sample set of data with an attempt to create the formula. There are two tabs, Results and Totals. I have my sample formula highlighted with an explanation in the Totals tab. Here is what I've tried but my results come back with #value! error, cannot find the error...

=SUMIFS(Results!E2:E3086,Results!C:C,">="&T2,Results!C:C,"<="&U2,Results!D:D,Totals!$A2)

=SUMIFS(Results!E2:E3086,Results!C:C,">="&T2,Results!C:C,"<="&U2,Results!D:D,Totals!$A2)

Hello Everyone ,

I have a requirement , I have a sheet A where I have 3 columns

Sheet A:

Column A Column B Column c

Asset 1 Details 1 Sale 1

Asset 2 Details 2 Sale 2

Asset 3 Details 3 Sale 3

Asset 4 Details 4 Sale 4

Asset 5 Details 5 Sale 5

Asset 6 Details 6 Sale 6

Asset 7 Details 7 Sale 7

Asset 8 Details 8 Sale 8

Asset 9 Details 9 Sale 9

Asset 10 Details 10 Sale 10

Asset 11 Details 11 Sale 11

Asset 12 Details 12 Sale 12

Asset 15 Details 15 Sale 15

Asset 20 Details 20 Sales 20

I have sheet B where I have 3 columns , but I have only Asset details ( Column A) present in this sheet . Column B and Column C details, I have to fetch it from Sheet A

Column A Column B Column C

Asset 15 Details 15 Sale 15

Asset 20 Details 20 Sale 20

How Can we fill the Sheet B Column B and C information

Thanks in Advance ..

]]>I have a requirement , I have a sheet A where I have 3 columns

Sheet A:

Column A Column B Column c

Asset 1 Details 1 Sale 1

Asset 2 Details 2 Sale 2

Asset 3 Details 3 Sale 3

Asset 4 Details 4 Sale 4

Asset 5 Details 5 Sale 5

Asset 6 Details 6 Sale 6

Asset 7 Details 7 Sale 7

Asset 8 Details 8 Sale 8

Asset 9 Details 9 Sale 9

Asset 10 Details 10 Sale 10

Asset 11 Details 11 Sale 11

Asset 12 Details 12 Sale 12

Asset 15 Details 15 Sale 15

Asset 20 Details 20 Sales 20

I have sheet B where I have 3 columns , but I have only Asset details ( Column A) present in this sheet . Column B and Column C details, I have to fetch it from Sheet A

Column A Column B Column C

Asset 15 Details 15 Sale 15

Asset 20 Details 20 Sale 20

How Can we fill the Sheet B Column B and C information

Thanks in Advance ..

I have an excel that need to Provide the range of data everyday. And I have 20 cells with different averaging column but the row is same. So, I need to manually change the range to every cells for 20 cells everyday.

Example: average(D5:D10), Average(I5:I10), Average(H5:H10).... and so on

and on the next day I need..

Average(D6:D11), Average(I6:I11), Average(H6:H11).... and so on.

So For this I need to Manually Type the Numbers everyday to all 20 cells.

Please Provide me a Formula that I will Type the Numbers like 5:10 or 6:11 to a cell and all the 20 cell copy the Numbers from the Given Cells as the Row remain same.

]]>Example: average(D5:D10), Average(I5:I10), Average(H5:H10).... and so on

and on the next day I need..

Average(D6:D11), Average(I6:I11), Average(H6:H11).... and so on.

So For this I need to Manually Type the Numbers everyday to all 20 cells.

Please Provide me a Formula that I will Type the Numbers like 5:10 or 6:11 to a cell and all the 20 cell copy the Numbers from the Given Cells as the Row remain same.

My data are all together and i dont know how to separate

Data,"last","Open","Máximum","Mínimum","Vol.","Var%"

23.12.2020,"117.806,85","116.636,18","118.311,44","116.636,18","6,48M","1,00%"

22.12.2020,"116.636,18","115.824,66","116.902,54","115.648,34","6,95M","0,70%"

21.12.2020,"115.822,57","118.020,94","118.020,94","114.730,05","10,35M","-1,86%"

all this are in the same line

Data,"last","Open","Máximum","Mínimum","Vol.","Var%"

23.12.2020,"117.806,85","116.636,18","118.311,44","116.636,18","6,48M","1,00%"

22.12.2020,"116.636,18","115.824,66","116.902,54","115.648,34","6,95M","0,70%"

21.12.2020,"115.822,57","118.020,94","118.020,94","114.730,05","10,35M","-1,86%"

all this are in the same line