Hello, I need some good advice

I have a spreadsheet with one column of data where the values range from 0 to 0.25. I want to count how many of these values falls in each of 5 intervals (>=0, >=0.05, >=0.10, >=0.15 and >=0.20)

Thanks very much

I have a spreadsheet with one column of data where the values range from 0 to 0.25. I want to count how many of these values falls in each of 5 intervals (>=0, >=0.05, >=0.10, >=0.15 and >=0.20)

Thanks very much

Hi - I hope someone can help me!

I need to write a formula where a list of values is returned based on an identified value in the same row.

So, I need to know on what dates was there a concurrency rate of 2.8 between 9am and 5pm (see attached excel). The formula must (a)return dates (without repeating dates), (b)only return results that occurred between 9-5 and (c) from the concurrency rate column only values of 2.8 should be included and (d) returns must appear on a different sheet in the same workbook.

So the end result should be a list of dates where this happened.

Anyone know how I go about this?

Thank you!!

I need to write a formula where a list of values is returned based on an identified value in the same row.

So, I need to know on what dates was there a concurrency rate of 2.8 between 9am and 5pm (see attached excel). The formula must (a)return dates (without repeating dates), (b)only return results that occurred between 9-5 and (c) from the concurrency rate column only values of 2.8 should be included and (d) returns must appear on a different sheet in the same workbook.

So the end result should be a list of dates where this happened.

Anyone know how I go about this?

Thank you!!

In the attached spreadsheet, I have a table that consist of Type, Wt & Conv. I need to find the Conv value based on matching type (B1) and closest match to wt (B2). In my example, the formula would return in cell B4 the value 4.85082. This is because the type matches the value of 1 and the Wt is rounded up to a value of 780 (B16) therefore formula returned 4.85082 which is located in C16.

How would one do this?

Thanks in advance for the help.

-Manny

How would one do this?

Thanks in advance for the help.

-Manny

The formulas on the column G calculates the taxes both based upon income K2 and the area It falls down (the column A).

I want a formula on the cell K4 which picks the tax calculated amount in the regarded area. (On eof the column A)

Since Income is 30000 (K2) (It falls the area of 53000, so the values up to 53000) the formula on the K4 has to return 8880.

How can I do this?

I want a formula on the cell K4 which picks the tax calculated amount in the regarded area. (On eof the column A)

Since Income is 30000 (K2) (It falls the area of 53000, so the values up to 53000) the formula on the K4 has to return 8880.

How can I do this?

Hello,

I have an excel sheet attached. I am trying to xlookup some search keys are comma-separated in excel.

What I would like to do is to SUM up all the returned values into a single cell. I dont know if this is possible for I have to use some VBA scripts.

I tried =XLOOKUP(F2,$A$1:$A$14,$B$1:$B$14)

Any ideas would be greatly appreciated.

a.JPG

I have an excel sheet attached. I am trying to xlookup some search keys are comma-separated in excel.

What I would like to do is to SUM up all the returned values into a single cell. I dont know if this is possible for I have to use some VBA scripts.

I tried =XLOOKUP(F2,$A$1:$A$14,$B$1:$B$14)

Any ideas would be greatly appreciated.

a.JPG

I need to work out the daily salary cost by apportioning each workers earnings equally over the period he worked. I tried using the sumifs function, but can't figure out how to reference a cell in the "criteria".

The desired result is shown in yellow on the uploaded spreadsheet.

Screenshot 2021-11-27 at 15.52.11.png

The desired result is shown in yellow on the uploaded spreadsheet.

Screenshot 2021-11-27 at 15.52.11.png

Hi,

So I'm doing some data analysis of interview transcripts. Currently these transcripts are in a word document but I need to copy and paste into Excel for analysis.

The difficultly I'm having is that I need each line of text to have a line number (for referencing) but when I copy and paste the data from word I either have a really wide column (which isn't workable) or if I use text wrap each line isn't in its own row so I can't have each line numbered.

I have attached an example workbook but I need 4 columns- A= Emergent Themes (which is currently blank), B= Line number (using formula =row(A1) C= My data (transcription) and D= Exploratory coding (blank).

As you can see in the example row 5 is no good as the text is all in one row, where I need each line to be in a different row in order for the line numbers to match up.

I've tried DATA -> FROM TABLE -> SPLIT COLUMN -> DELIMINATOR -> CUSTOM -> SPECIAL CHARACTER etc. but it doesn't work.

Any ideas? Please message if you need any clarification.

Thanks in advance.

So I'm doing some data analysis of interview transcripts. Currently these transcripts are in a word document but I need to copy and paste into Excel for analysis.

The difficultly I'm having is that I need each line of text to have a line number (for referencing) but when I copy and paste the data from word I either have a really wide column (which isn't workable) or if I use text wrap each line isn't in its own row so I can't have each line numbered.

I have attached an example workbook but I need 4 columns- A= Emergent Themes (which is currently blank), B= Line number (using formula =row(A1) C= My data (transcription) and D= Exploratory coding (blank).

As you can see in the example row 5 is no good as the text is all in one row, where I need each line to be in a different row in order for the line numbers to match up.

I've tried DATA -> FROM TABLE -> SPLIT COLUMN -> DELIMINATOR -> CUSTOM -> SPECIAL CHARACTER etc. but it doesn't work.

Any ideas? Please message if you need any clarification.

Thanks in advance.

Hi, I'm currently using SUMIFS to total data, my workbook is getting very calculation-heavy, and I'm replacing formulas with dynamic array formulas.

I have raw data in the first table. The table at cell M36 shows the data correctly using SUMIFS, and the table at cell C36 is where I'm trying to use a dynamic array formula, unsuccessfully (cell D37).

Any suggestions appreciated. TIA. Paul

I have raw data in the first table. The table at cell M36 shows the data correctly using SUMIFS, and the table at cell C36 is where I'm trying to use a dynamic array formula, unsuccessfully (cell D37).

Any suggestions appreciated. TIA. Paul

Hi,

Please see the attached file. I might not be able to explain properly but when you see the sheet (Column in Red Color, you will understand what I am asking for.

I have a table from A6 to M16 and in I want from N6 to N16, the value in K1, from O6 to O16, the value in K2, from P6 to P16, the value in K3, from Q6 to Q16, the value in K4

Once the value in K changes (i.e., Ref Bill No) then the value in N to Q column in the table below it to change accordingly. Since tables are dynamic, I am unable to use fixed formulas.

Please help.

PS:Can this be done formula based or should I seek VBA help

Please see the attached file. I might not be able to explain properly but when you see the sheet (Column in Red Color, you will understand what I am asking for.

I have a table from A6 to M16 and in I want from N6 to N16, the value in K1, from O6 to O16, the value in K2, from P6 to P16, the value in K3, from Q6 to Q16, the value in K4

Once the value in K changes (i.e., Ref Bill No) then the value in N to Q column in the table below it to change accordingly. Since tables are dynamic, I am unable to use fixed formulas.

Please help.

PS:Can this be done formula based or should I seek VBA help

In the assignment I have been asked 6 questions, I am confused as to what formulas would best work. I have been asked to use formulas and not manually solve the questions. (This is a practice assignment I found on the internet and this is not a homework assignment, I am not a student, I am learning Excel on my own.)

Hi there,

In a cell of Sheet1, i have this formula:

=OFFSET(Sheet2!$D$1,LARGE(IF(Sheet2!$F$3:F$1033>0,ROW(Sheet2!$D$3:D$1033)),$A12)-1,0,ROWS(Sheet2!$F$3:F$1033),4)

It works when Sheet2 data is not using the function Indirect (if data is manually entered, the above formula in sheet1 function and display the result. But if I use formula Indirect in the Sheet2, then formula in sheet1 will display nothing.

But in sheet2,

result in column D is date, ex: D1 =INDIRECT("'0" & A3 & "'!a1") (and the result is date, ex: 14/11/2021)

result in column F is text, ex: F1 = INDIRECT("'0" & A3 & "'!a2") (and example of result is: University)

Not working with these trials:

I have tried Ctrl+Shift+enter

I have tried n(Indirect(... or n(offset(...

I have tried Iterative Calculation on

I also tried copy value only of sheet2 to sheet 3, and Offset in sheet1 refer to sheet3 instead of sheet2

I have tried reset columns/cells format type to "general" or the "date" and "text" or "number" accordingly or "automatic"

Does it mean that offset can't take data from Indirect?

Or I have missed something?

thanks a lot

txt007

]]>In a cell of Sheet1, i have this formula:

=OFFSET(Sheet2!$D$1,LARGE(IF(Sheet2!$F$3:F$1033>0,ROW(Sheet2!$D$3:D$1033)),$A12)-1,0,ROWS(Sheet2!$F$3:F$1033),4)

It works when Sheet2 data is not using the function Indirect (if data is manually entered, the above formula in sheet1 function and display the result. But if I use formula Indirect in the Sheet2, then formula in sheet1 will display nothing.

But in sheet2,

result in column D is date, ex: D1 =INDIRECT("'0" & A3 & "'!a1") (and the result is date, ex: 14/11/2021)

result in column F is text, ex: F1 = INDIRECT("'0" & A3 & "'!a2") (and example of result is: University)

Not working with these trials:

I have tried Ctrl+Shift+enter

I have tried n(Indirect(... or n(offset(...

I have tried Iterative Calculation on

I also tried copy value only of sheet2 to sheet 3, and Offset in sheet1 refer to sheet3 instead of sheet2

I have tried reset columns/cells format type to "general" or the "date" and "text" or "number" accordingly or "automatic"

Does it mean that offset can't take data from Indirect?

Or I have missed something?

thanks a lot

txt007

I have written code to convert positive values in Col C to negative and vice Versa

However when running my macro, I get a run time error, "type mismatch" and the code below is highlighted

See Full code below

It would be appreciated if someone could amend my code

However when running my macro, I get a run time error, "type mismatch" and the code below is highlighted

Code:

`.Range("C2:C" & LR).Value = .Range("C2:C" & LR).Value * -1`

See Full code below

Code:

` Sub ChangeValueSign()`

Dim LR As Long

With Sheets("Imported Data")

LR = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("C2:C" & LR).Value = .Range("C2:C" & LR).Value * -1

End With

End Sub

It would be appreciated if someone could amend my code

I have a table that consists of incremental numbers: the first number is entered by the user and all others are just increments of the preceding number. Basically, each cell takes the value of the preceding cell and adds 1 to its value: B5+1, C5+1, D5+1 and so on. The problem that I have is that the initial number can have a leading zero and if that happens, I need to keep that zero with all incremental numbers until the zero needs to be replaced by "1" in the incremental count. The size of the numbers is specified by the initial number: if the initial number has 4 digits, then all others should keep the 4-digit size, regardless of how many zeroes it begins with. For example: if the initial number is "01", then the next one should be "02". If the initial number is "0001", then the next one should be "0002".

]]>Hi everyone, long time lurker, first time poster.

(thank you for all the help in the past).

I have eaten too much turkey for thanksgiving, and I am majorly over complicating something I know is a lot simpler than my head thinks it is.

Total Spend = $75,000,000

Total transactions = 150,000

Therefore avg transaction spend = $500.

There are then two channels of which you can buy (channel A and channel B).

I know 70% of transactions go through Channel A.

And I know Channel A on average costs 20% more than Channel B.

How do I calculate the average prices for Channel A and the average price for Channel B?

Thanks in advance =)

Screenshot 2021-11-26 at 20.44.18.png

]]>(thank you for all the help in the past).

I have eaten too much turkey for thanksgiving, and I am majorly over complicating something I know is a lot simpler than my head thinks it is.

Total Spend = $75,000,000

Total transactions = 150,000

Therefore avg transaction spend = $500.

There are then two channels of which you can buy (channel A and channel B).

I know 70% of transactions go through Channel A.

And I know Channel A on average costs 20% more than Channel B.

How do I calculate the average prices for Channel A and the average price for Channel B?

Thanks in advance =)

Screenshot 2021-11-26 at 20.44.18.png

Hello,

I am trying to find a value between a range but unable to do that, i tried Index & match combination too but unable....

Level 0 10 20 30 40 50 60 70 80 90 100

Volume 0 0.34 0.97 1.75 2.63 3.55 4.47 5.35 6.13 6.76 7.1

I need to find Volume when Level will be 35? I want to use Level and Volume in dynamic range....

Please give me the solution....

regards,

]]>I am trying to find a value between a range but unable to do that, i tried Index & match combination too but unable....

Level 0 10 20 30 40 50 60 70 80 90 100

Volume 0 0.34 0.97 1.75 2.63 3.55 4.47 5.35 6.13 6.76 7.1

I need to find Volume when Level will be 35? I want to use Level and Volume in dynamic range....

Please give me the solution....

regards,