Hello friends.

I have a complicated task in front of me that I've been trying to solve for a long time and I can't figure it out.

It's a big challenge and I'm worried if it's even technically possible.

At work, I collect data in excel tables, which are also separate files. In these tables, I enter the work performed, where each work task is assigned a position number. Under this number, I have the price for the work done in another table.

What I need is to select from each table the values with a certain number with a quantity associated with it. Take these values with a given number, for example "1.1.1", from each table and add them to one table where all these numbers are sorted. What I want to achieve is that I will see in one table what the total value of the position will be, for example "1.1.1" from all tables.

I know it's complicated, but if someone could find a way to implement it through formulas, I would be very grateful.

well thank you

Sincerely

Roman

I have a complicated task in front of me that I've been trying to solve for a long time and I can't figure it out.

It's a big challenge and I'm worried if it's even technically possible.

At work, I collect data in excel tables, which are also separate files. In these tables, I enter the work performed, where each work task is assigned a position number. Under this number, I have the price for the work done in another table.

What I need is to select from each table the values with a certain number with a quantity associated with it. Take these values with a given number, for example "1.1.1", from each table and add them to one table where all these numbers are sorted. What I want to achieve is that I will see in one table what the total value of the position will be, for example "1.1.1" from all tables.

I know it's complicated, but if someone could find a way to implement it through formulas, I would be very grateful.

well thank you

Sincerely

Roman

Hi

i have an issue with parts of cells.

I have a few data with different types:

AMII

TCB I

MIIIo

then i have different heights

33/36

15/18

and

different %

0%

50%

100%

I would like to have this 3 items in 3 different columns

Attached pls find a excel.

Many thanks in advance

i have an issue with parts of cells.

I have a few data with different types:

AMII

TCB I

MIIIo

then i have different heights

33/36

15/18

and

different %

0%

50%

100%

I would like to have this 3 items in 3 different columns

Attached pls find a excel.

Many thanks in advance

Hi all,

I am trying to get a formula that will fill up column F automatically.

column F is a cumulative sum of column E up to closest and late date where there is a number in column E.

I am trying to get a formula that will fill up column F automatically.

column F is a cumulative sum of column E up to closest and late date where there is a number in column E.

Looking for a non-array to look into column E and extract all values before the dash to the right. Desired outcome is in column F. See attached sample.

Thanks

Thanks

Hi Everyone,

I'm importing data from a MS-DOS era database txt file export. There are 17000 records with 19 possible data fields each. However, if a field wasn't populated in the DOS database, the export doesn't include a space for it in the export, which means the order of the fields vary, and therefore can't be transposed. One record might have A, B, C, D fields, and the next one has A, C, D (I.e., B is missing)

Does anyone know how I could accomplish something like "If a cell with the value A is not followed by a cell with the value B, insert blank row"?

Here is an example of the first four lines from two records:

TN

CN

TI

G

and

TN

CN

ST

TI

In this example, the first record is missing a field called "ST", which should be after CN. I'd like run a command that says "If CN is not followed by ST, insert blank row"

THANK YOU!

]]>I'm importing data from a MS-DOS era database txt file export. There are 17000 records with 19 possible data fields each. However, if a field wasn't populated in the DOS database, the export doesn't include a space for it in the export, which means the order of the fields vary, and therefore can't be transposed. One record might have A, B, C, D fields, and the next one has A, C, D (I.e., B is missing)

Does anyone know how I could accomplish something like "If a cell with the value A is not followed by a cell with the value B, insert blank row"?

Here is an example of the first four lines from two records:

TN

CN

TI

G

and

TN

CN

ST

TI

In this example, the first record is missing a field called "ST", which should be after CN. I'd like run a command that says "If CN is not followed by ST, insert blank row"

THANK YOU!

Using the following formula

=IF(COUNTIF($W$35:$W$50,$L$2)>0,"",L3)

but now we need it to find the same cell twice in that range before considering the equation true. Not sure how it would be done. Tried AND function but it just uses the 1 cell to satisfy both equations. Made notes in the sample spreadsheet. TY for looking.

=IF(COUNTIF($W$35:$W$50,$L$2)>0,"",L3)

but now we need it to find the same cell twice in that range before considering the equation true. Not sure how it would be done. Tried AND function but it just uses the 1 cell to satisfy both equations. Made notes in the sample spreadsheet. TY for looking.

I am hoping someone can help. I'm trying to create a spreadsheet where I keep track of stocks. I want to balance the spreadsheet against monthly statements. In one of the cells, I'm trying to calculate the value of stocks that have not sold yet however, the formula seems to be having a hard time recognizing cells with no dates for "Date Sold".

When calculating the "Unrealized Value" cell, I'm trying to sum the "Sell Total (No Fees)" column based on

1. Stocks purchased on or after the start date

2. Stocks purchased on or before the end date

3. Stocks with a sell date on or before the end date

4. Stocks which have blank cells for sell dates.

It seems the last one is giving me issues.

Here is the data I'm using...

Cell F7 = Start Date

Cell G7 = End Date

A separate worksheet contains the stock info in a table called StockList

Table Columns used for ranges / criteria in formula = Sell Total (No Fees), Date Bought, Date Sold.

Here is the formula I am using.

=IF($G$7<>"",SUMIFS(StocksList[Sell Total (No Fees)],StocksList[Date Sold],"",StocksList[Date Bought],">="&$F$7,StocksList[Date Bought],"<="&$G$7),SUMIFS(StocksList[Sell Total (No Fees)],StocksList[Date Sold],"",StocksList[Date Bought],">0"))

If someone sees something I'm doing wrong in the formula, I'd really appreciate the feedback!

Thx!

]]>When calculating the "Unrealized Value" cell, I'm trying to sum the "Sell Total (No Fees)" column based on

1. Stocks purchased on or after the start date

2. Stocks purchased on or before the end date

3. Stocks with a sell date on or before the end date

4. Stocks which have blank cells for sell dates.

It seems the last one is giving me issues.

Here is the data I'm using...

Cell F7 = Start Date

Cell G7 = End Date

A separate worksheet contains the stock info in a table called StockList

Table Columns used for ranges / criteria in formula = Sell Total (No Fees), Date Bought, Date Sold.

Here is the formula I am using.

=IF($G$7<>"",SUMIFS(StocksList[Sell Total (No Fees)],StocksList[Date Sold],"",StocksList[Date Bought],">="&$F$7,StocksList[Date Bought],"<="&$G$7),SUMIFS(StocksList[Sell Total (No Fees)],StocksList[Date Sold],"",StocksList[Date Bought],">0"))

If someone sees something I'm doing wrong in the formula, I'd really appreciate the feedback!

Thx!

How to make excel automatically find the formula for selected cells

]]>Hi all, I'm desperately looking for a formula that calculates the end date and time given the duration (Hours). This must be calculated according to working days only (mon-Fry), with variable working hours (e.g. 4.5hrs/day)

Please see below an example:

GIVEN DATA

Start Date and Time: 01/03/2023 09:00

Task Duration: 11:00 (Hours)

Begin of Working Day: 09:00, End of Working Day: 13:30, equals to 4.5 working hours/day

5 Working Days/Week

OUTPUT Should be:

End Date and Time; 03/03/2023 11:00 (as it is 2 days * 4,5hours=9hours + 2hours on the third day

I'm new to the forum so please let me know if this is acceptable

thanks very much for your help

]]>Please see below an example:

GIVEN DATA

Start Date and Time: 01/03/2023 09:00

Task Duration: 11:00 (Hours)

Begin of Working Day: 09:00, End of Working Day: 13:30, equals to 4.5 working hours/day

5 Working Days/Week

OUTPUT Should be:

End Date and Time; 03/03/2023 11:00 (as it is 2 days * 4,5hours=9hours + 2hours on the third day

I'm new to the forum so please let me know if this is acceptable

thanks very much for your help

Hi everyone.

Im trying to find the average of the top values of several- non continued ranges. see image for reference:

Annotation 2023-01-28 164421.png

For example, I need to average the top value of range B4:B14 with the top value of B16:26 and so on and so forth. So basically skipping once cell every 11 cells.

On the cell D4 I have shown the formula I have been using to only average EVERY 12 cells ( the opposite which is average every yellow cell basically) for the Row C:C

Formula example: =AVERAGE(IF(MOD(ROW(C3:C1904)-ROW(C3),12)=0,IF(C3:C1904<>"",C3:C1904)))

Im kinda lost on how to do what im looking for. I have tried just modding my original formula with the MOD/ROW functions alongside AVERAGE and LARGE. but I kinda gave up. If you guys have any ideas, they are all welcome.

Test Sample.xlsxTest Sample.xlsx

]]>Im trying to find the average of the top values of several- non continued ranges. see image for reference:

Annotation 2023-01-28 164421.png

For example, I need to average the top value of range B4:B14 with the top value of B16:26 and so on and so forth. So basically skipping once cell every 11 cells.

On the cell D4 I have shown the formula I have been using to only average EVERY 12 cells ( the opposite which is average every yellow cell basically) for the Row C:C

Formula example: =AVERAGE(IF(MOD(ROW(C3:C1904)-ROW(C3),12)=0,IF(C3:C1904<>"",C3:C1904)))

Im kinda lost on how to do what im looking for. I have tried just modding my original formula with the MOD/ROW functions alongside AVERAGE and LARGE. but I kinda gave up. If you guys have any ideas, they are all welcome.

Test Sample.xlsxTest Sample.xlsx

I am stuck with the filtering. I need to filter the top 3 repeating texts in a table and sort them out by groups.

I have 3 tables Check In, Percent and Missing Points. I need to filter from the Missing points section the top 3 repeating texts and sort them out per section "What" was is in the Check in or Rooming

What Percentage Missed points

Check In 85.71% Registration Card with Data privacy are sighned / Guest book is provided and explaine / Check In in Protel is done and room keys encoded till 14:00

Check In 92.86% Special remarks are mentioned to the guest (Suitebooker Benefits, Partner Benefits) /Offered to take care of the jackets (to bring to the room) 0

Check In 67.86% Ask for the passport / Serve Welcome drink / Welcome drink was presented in a Chenot way

Check In 85.71% Went through the reservation details /Verify if the My Chenot App is downloaded /welcome letter and Info sheet is explained

Rooming 75.00% t i s

Rooming 89.29% i s l

]]>I have 3 tables Check In, Percent and Missing Points. I need to filter from the Missing points section the top 3 repeating texts and sort them out per section "What" was is in the Check in or Rooming

What Percentage Missed points

Check In 85.71% Registration Card with Data privacy are sighned / Guest book is provided and explaine / Check In in Protel is done and room keys encoded till 14:00

Check In 92.86% Special remarks are mentioned to the guest (Suitebooker Benefits, Partner Benefits) /Offered to take care of the jackets (to bring to the room) 0

Check In 67.86% Ask for the passport / Serve Welcome drink / Welcome drink was presented in a Chenot way

Check In 85.71% Went through the reservation details /Verify if the My Chenot App is downloaded /welcome letter and Info sheet is explained

Rooming 75.00% t i s

Rooming 89.29% i s l

I have a user-changeable list of text values - let's say they're staff members and the list may shorten or lengthen at any time. No problem.

On my worksheet there is a column (D) that counts the occurrence of the mentioned text values in a range using COUNTIF. (It's actually a range/partial column (D23:D38) - there is data above and below it in the sheet, but that data is not relevant to this part of the sheet (except in the sense that the column is therefore not empty of data above and below my range).

Next I have a column E (also not a full column - E23:E38) that duplicates the text values that occur somewhere in the range, but omit the text values that do not - a simple IF NOT <> formula suffices here.

This is where my problem comes in. I sort column E according to the values in column D, which gives me a final column with the text values arranged according to the number of times they occur in te range. I use SORTBY for this, and I end up with a list that IS sorted, but it also places my text values right smack dab in the vertical middle of the range. Blank cells above, blank cells below. It's these blank cells that are my problem.**This list needs to start in the topmost cell of this range.**

I've thought of filtering out the spaces first, but I cannot figure out how to remove spaces from column D (I assume the cells need to be truly empty; not zero or something else). I would need to remove them from both D and E to maintain the relative positions for sorting.

Sample file attached.

On my worksheet there is a column (D) that counts the occurrence of the mentioned text values in a range using COUNTIF. (It's actually a range/partial column (D23:D38) - there is data above and below it in the sheet, but that data is not relevant to this part of the sheet (except in the sense that the column is therefore not empty of data above and below my range).

Next I have a column E (also not a full column - E23:E38) that duplicates the text values that occur somewhere in the range, but omit the text values that do not - a simple IF NOT <> formula suffices here.

This is where my problem comes in. I sort column E according to the values in column D, which gives me a final column with the text values arranged according to the number of times they occur in te range. I use SORTBY for this, and I end up with a list that IS sorted, but it also places my text values right smack dab in the vertical middle of the range. Blank cells above, blank cells below. It's these blank cells that are my problem.

I've thought of filtering out the spaces first, but I cannot figure out how to remove spaces from column D (I assume the cells need to be truly empty; not zero or something else). I would need to remove them from both D and E to maintain the relative positions for sorting.

Sample file attached.

Hi, thanks in advance for your help with a very simple problem for a new Excel user.

Let's say a new amount is $100 and $100 was a X% increase from the former amount, what formula would I use to calculate the former amount? Conversely, if $100 is a X% decrease from the former amount, what formula would I use to calculate the former amount?

In addition to the formula, I would appreciate knowing the two equations as well. Thanks again, I'm a writer and math is not my strong suit.

]]>Let's say a new amount is $100 and $100 was a X% increase from the former amount, what formula would I use to calculate the former amount? Conversely, if $100 is a X% decrease from the former amount, what formula would I use to calculate the former amount?

In addition to the formula, I would appreciate knowing the two equations as well. Thanks again, I'm a writer and math is not my strong suit.

hello all.

im racking my brain out here and cant seem to get the correct formula.

attached a workbook for you to review

im trying to find a formula to combine data values from multiple rows to one row (where the data value is the same in one column).

a standard Index Match formula will work if there is only 2 rows of data, im working with 4+ rows of data.

any pointers on this one.

thank you all in advance.

edit

sorry i forgot to expand on this one.

the data set is extracted using power query and the actual data is time / shift start time, shift finish time and the columns are the days in the week.

i cant group the rows together for this reason.

im racking my brain out here and cant seem to get the correct formula.

attached a workbook for you to review

im trying to find a formula to combine data values from multiple rows to one row (where the data value is the same in one column).

a standard Index Match formula will work if there is only 2 rows of data, im working with 4+ rows of data.

any pointers on this one.

thank you all in advance.

edit

sorry i forgot to expand on this one.

the data set is extracted using power query and the actual data is time / shift start time, shift finish time and the columns are the days in the week.

i cant group the rows together for this reason.

Hello guys,

trying to figure out how I use excel to calculate the below.

Missing Duplicate

User_1 12 3 5 6 8 9 10 11 15 17 20

User_2 1 5 6 2 11 14 17

User_3 1 2 5 7 11 12 15 17 18 19

User_4 6 3 4 5 7 8 10 11 12 14 15 17 18 19 20

User_5 1 5 6 2 11 14 17

User_6 2 8 9 14 16

User_7 12 3 5 6 8 9 10 11 15 17 20

User_8 8 14 2 3 5 7 9 13 15 16 17

User_9 4 6 7 5 8 10 11 12 15 16 17 19 20

1 - Each user has a few missing numbers, also duplicate ones.

Example: User_1 has 12 as missing, User_9 has it, but user_3 has it too as well user_4.

2 - Each user can exchange just one number with another user

Is It possible to use any formula, so excel can suggest the best way to accomplish that?

]]>trying to figure out how I use excel to calculate the below.

Missing Duplicate

User_1 12 3 5 6 8 9 10 11 15 17 20

User_2 1 5 6 2 11 14 17

User_3 1 2 5 7 11 12 15 17 18 19

User_4 6 3 4 5 7 8 10 11 12 14 15 17 18 19 20

User_5 1 5 6 2 11 14 17

User_6 2 8 9 14 16

User_7 12 3 5 6 8 9 10 11 15 17 20

User_8 8 14 2 3 5 7 9 13 15 16 17

User_9 4 6 7 5 8 10 11 12 15 16 17 19 20

1 - Each user has a few missing numbers, also duplicate ones.

Example: User_1 has 12 as missing, User_9 has it, but user_3 has it too as well user_4.

2 - Each user can exchange just one number with another user

Is It possible to use any formula, so excel can suggest the best way to accomplish that?