Hi there, i have a table of data that has times from 00:00:00 to 23:59:00 with volumes of power usage for 5 minute intervals. I am trying to use a sumif function to sum peak and offpeak amounts using time:

Peak = SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=07:00:00",Mvmt!$B$3:$B$8353,"<=20:59:00")

Where column F= is my usage to sum and column B = the 5 minute time interval.

Offpeak = SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=21:00:00",Mvmt!$B$3:$B$8353,"<=06:59:00")

I suspect it has something to do with a 24 hour clock and it not knowing that 00:00 is after 23:00 but cant work a better formula.

Any ideas?

Ta

]]>Peak = SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=07:00:00",Mvmt!$B$3:$B$8353,"<=20:59:00")

Where column F= is my usage to sum and column B = the 5 minute time interval.

Offpeak = SUMIFS(Mvmt!F3:F8353,Mvmt!$B$3:$B$8353,">=21:00:00",Mvmt!$B$3:$B$8353,"<=06:59:00")

I suspect it has something to do with a 24 hour clock and it not knowing that 00:00 is after 23:00 but cant work a better formula.

Any ideas?

Ta

Hi All

Have a formula

"=IFERROR(IF(SEARCH("Evening",B7,1),0),IFERROR(VLOOKUP(D7,'Program Days and Times'!C:I,5,FALSE)," "))"

I need to search for evening and Saturday - how can I add this please?

Regards

]]>Have a formula

"=IFERROR(IF(SEARCH("Evening",B7,1),0),IFERROR(VLOOKUP(D7,'Program Days and Times'!C:I,5,FALSE)," "))"

I need to search for evening and Saturday - how can I add this please?

Regards

I have a large dataset and trying to use the new GROUPBY formula to aggregate sales data spread over multiple columns (months) into quarters. I know, a straightforward solution is to insert a subtotal col per quarter, but it should be a lot easier if the SUM aggregation would work as the normal SUM formula, which aggregates all cells in the array provided, e.g. SUM(A1:D10) counts all cells into a single result.

Enclosed is a sample dataset with the formula. As you see, the GROUPBY formula does aggregate vertical data, but leaves the columns that must be aggregates as well intact. I have tried several options, but in all times the outcome is not what I want (aggregated quarter sales data)

Art.

Enclosed is a sample dataset with the formula. As you see, the GROUPBY formula does aggregate vertical data, but leaves the columns that must be aggregates as well intact. I have tried several options, but in all times the outcome is not what I want (aggregated quarter sales data)

Art.

Hi

I am trying to do a conditional formatting on a column that fills red if a number is entered that does not equal 0.10 or 0.25.

I have tried numerous different formulas, but none of them are working

The cell already has lookup formulas in it, i just want to be able to highlight if they are not 0.10 or 0.25

]]>I am trying to do a conditional formatting on a column that fills red if a number is entered that does not equal 0.10 or 0.25.

I have tried numerous different formulas, but none of them are working

The cell already has lookup formulas in it, i just want to be able to highlight if they are not 0.10 or 0.25

I currently have a formula below that works for calculating the monthly salary based on start and end date. Here it is....

IF(OR($B2>EOMONTH(E$1,0),$C2<E$1),"",$D2*(MIN(EOMONTH(E$1,0),$C2)-MAX(E$1,$B2)+1)/IF(MOD(YEAR($E$1),4)=0,366,365))

This calculates the monthly salary based on the number of days in the month compared to the annual salary. As a result, each monthly salary is different based on the number of days.

I would like to change this formula so that the monthly salary is the same each month except for partial months (either the start date month or the end date month). In this case, the prorated salary for the month that has a partial date should calculate based on the # of days in month as a % of total days in that month.

For example, a person with annual salary of $120,000 has a monthly salary of $10,000. If the person is starting on May 15, 2024, the salary cost for the month of May is $5,484. From May 15th through May 31st is 17 days, so the cost is equal to 17*(10,000/31). I would then want to see a monthly salary cost of $10,000 from June onwards until the person leaves. The month they leave would be prorated similarly.

Can anyone help with this update to the above formula? Thanks!

]]>IF(OR($B2>EOMONTH(E$1,0),$C2<E$1),"",$D2*(MIN(EOMONTH(E$1,0),$C2)-MAX(E$1,$B2)+1)/IF(MOD(YEAR($E$1),4)=0,366,365))

This calculates the monthly salary based on the number of days in the month compared to the annual salary. As a result, each monthly salary is different based on the number of days.

I would like to change this formula so that the monthly salary is the same each month except for partial months (either the start date month or the end date month). In this case, the prorated salary for the month that has a partial date should calculate based on the # of days in month as a % of total days in that month.

For example, a person with annual salary of $120,000 has a monthly salary of $10,000. If the person is starting on May 15, 2024, the salary cost for the month of May is $5,484. From May 15th through May 31st is 17 days, so the cost is equal to 17*(10,000/31). I would then want to see a monthly salary cost of $10,000 from June onwards until the person leaves. The month they leave would be prorated similarly.

Can anyone help with this update to the above formula? Thanks!

I have a spreadsheet which has increasing rows as data is added.

Using one column(H) I want to use the last 40 entries to find the value in one of these cells that appears the most times in the column.

I have used the following formula to find the current last cell =LOOKUP(2,1/((H:H)),ROW(H:H)) which is (178) this is in cell N154,

Then i use `=(N154-40)` to find the cell 40 rows up,

then use `="H"&(N154)` to define thr row/column ie `(H178)`

and use `="H"&(N154-40)` to get start cell ie `(H138)`.

So in J154 i have (H138) and in cell K154 i have (H178)

I want to use the values of the above cells `J154` and `K154` respectively in the formula

`=INDEX(J154:K154,MODE(MATCH(J154:K154,J154:K154,0)))` to get the number of cells with the same value?

Cannot get this formula to work so would be grateful for any advice re this ?

Regards

Paul (stone1048)

]]>Using one column(H) I want to use the last 40 entries to find the value in one of these cells that appears the most times in the column.

I have used the following formula to find the current last cell =LOOKUP(2,1/((H:H)),ROW(H:H)) which is (178) this is in cell N154,

Then i use `=(N154-40)` to find the cell 40 rows up,

then use `="H"&(N154)` to define thr row/column ie `(H178)`

and use `="H"&(N154-40)` to get start cell ie `(H138)`.

So in J154 i have (H138) and in cell K154 i have (H178)

I want to use the values of the above cells `J154` and `K154` respectively in the formula

`=INDEX(J154:K154,MODE(MATCH(J154:K154,J154:K154,0)))` to get the number of cells with the same value?

Cannot get this formula to work so would be grateful for any advice re this ?

Regards

Paul (stone1048)

Hello,

I have a formula that when entered into a scientific calculator it gives the desired results. BUT when I try to replicate this formula in Excel I can NOT get the desired results.

I know I must be doing something wrong.

Here is the formula copied from the calculator:

(sqrt((228.6+84)^2-(228.6-40)^2)-sqrt((228.6+sqrt(84^2-(40*sin(45))^2))^2-(228.6-40*cos(45))^2)

It gives the answer 15.73773604926337 Which is the desired answer

Formula.jpg

When I try to duplicate this in excel I do this:

=+SQRT((228.6+84)^2-(228.6-40)^2)-SQRT((228.6+SQRT(84^2-(40*SIN(45))^2))^2-(228.6-40*COS(45))^2)

Which returns 25.3011

Can someone tell me what I am misunderstanding?

Thank you

Mark

]]>I have a formula that when entered into a scientific calculator it gives the desired results. BUT when I try to replicate this formula in Excel I can NOT get the desired results.

I know I must be doing something wrong.

Here is the formula copied from the calculator:

(sqrt((228.6+84)^2-(228.6-40)^2)-sqrt((228.6+sqrt(84^2-(40*sin(45))^2))^2-(228.6-40*cos(45))^2)

It gives the answer 15.73773604926337 Which is the desired answer

Formula.jpg

When I try to duplicate this in excel I do this:

=+SQRT((228.6+84)^2-(228.6-40)^2)-SQRT((228.6+SQRT(84^2-(40*SIN(45))^2))^2-(228.6-40*COS(45))^2)

Which returns 25.3011

Can someone tell me what I am misunderstanding?

Thank you

Mark

I am unable to find a mix of formulae for my problem. I have a Matrix which contains Batch numbers and Product Codes. The quantities I want to sum up are listed right under the respective product code and the batch reference is listed to the right of every quantity value.

Then in another list I have the product code and the batch numbers listed on separate rows.

I would like to achieve the following: Excel to sum up the values underneath the product code if the product code in the column header matches the one in the list and if there is a match between the batch numbers as well. Please have a look at the attachment and let me know how I could do that.

Then in another list I have the product code and the batch numbers listed on separate rows.

I would like to achieve the following: Excel to sum up the values underneath the product code if the product code in the column header matches the one in the list and if there is a match between the batch numbers as well. Please have a look at the attachment and let me know how I could do that.

hi

I have a table

and I want to know the highest Row number and the lowest Row number (not the data I added, the number of the row(excel) ).

for this, I use =MAX(ROW(table)) but it only shows the 1st row number of the table ! not the last one

can anyone help me with this?

]]>I have a table

and I want to know the highest Row number and the lowest Row number (not the data I added, the number of the row(excel) ).

for this, I use =MAX(ROW(table)) but it only shows the 1st row number of the table ! not the last one

can anyone help me with this?

Dear Team,

Require formula help to reflect value in table 1 from table 2 in same row matching criteria in table 1.

Need your help on to reflect the cell value of same row matching with month as criteria in first table in cell no. D5 against table 2. In give data set if I select month Apr 23 in table 1 ( D5), corresponding data in table 2 of April23 (column G) should capture in D column against each fruit in same line. Demo file is attached for your reference.

Hope I could explain my query.

Thanks in advance.

Require formula help to reflect value in table 1 from table 2 in same row matching criteria in table 1.

Need your help on to reflect the cell value of same row matching with month as criteria in first table in cell no. D5 against table 2. In give data set if I select month Apr 23 in table 1 ( D5), corresponding data in table 2 of April23 (column G) should capture in D column against each fruit in same line. Demo file is attached for your reference.

Hope I could explain my query.

Thanks in advance.

I built a BP tracker about 4 years ago, and have added a feature here and there over time. One of the features is an array which shows the dates for a week. After cutting out all the other sheets I've figured out this column bogs down the computer significantly on saves (I've had to disable auto calculation in order for it to work). In the full sheet, with ~5,100 entries and a similar formula on another sheet, it'll take roughly 8 minutes to save on my home computer (an Alienware), and ~31 minutes on my work computer (basic HP). Is there a way I can achieve the same result without the lag?

hi Team ,

I have attached sheet for your reference.

I want formula in E2 : E10 and E2 need a formula to look for A2 employee id with C2 course across the table and should get how many times with this combination the entries are . For example : A2 employee has AZ-500 certification in two different dates so i need 2 should reflect in E2 & E3. Like wise for other employees also it should look for combination and get the no of times non column E.

I have attached sheet for your reference.

I want formula in E2 : E10 and E2 need a formula to look for A2 employee id with C2 course across the table and should get how many times with this combination the entries are . For example : A2 employee has AZ-500 certification in two different dates so i need 2 should reflect in E2 & E3. Like wise for other employees also it should look for combination and get the no of times non column E.

Below is a simple text string that is all in one cell and shows trnr: (for trainer), last name, first name, middle initial (not always shown) and in parenthesis the # of races , # of wins, # of 2nds, # of 3rds, and the winning percentage.

Trnr: Silva Juan P (48 9-8-5 19%)

The formula to return the number of races (48) that the trainer has entered horses in is shown below. Is there a cleaner way to write it and get the same result?

I also want to return the trainers winning percentage (19%) and listed below is the formula. Is there a cleaner or simpler way to write it and get the same result?

]]>Trnr: Silva Juan P (48 9-8-5 19%)

The formula to return the number of races (48) that the trainer has entered horses in is shown below. Is there a cleaner way to write it and get the same result?

Formula:

=--TEXTAFTER(TEXTBEFORE(TEXTAFTER(A5," ",-3)," "),"(")

I also want to return the trainers winning percentage (19%) and listed below is the formula. Is there a cleaner or simpler way to write it and get the same result?

Formula:

=--LEFT(TEXTAFTER(A5," ",-1),FIND(")",TEXTAFTER(A5," ",-1))-1)

Can someone please help me ? It would be much appreciated. Thanks in advance

Hi,

Need your help to get formula to filter out empty rows if range from Column B to Column F is empty. Cells from Column B to F could be formula returned empty values.

Screenshot 2024-03-03 065932.png

Appreciate for your help.

]]>Need your help to get formula to filter out empty rows if range from Column B to Column F is empty. Cells from Column B to F could be formula returned empty values.

Screenshot 2024-03-03 065932.png

Appreciate for your help.