Hello Forum

I have the following Table, it is to organize private classes.

Classify the students by Name (by colors I did not know how to achieve it) and € / Hours

I have the other table that is Week and where depending on the time I indicate the name and knowing the price (€ / h), it tells me some values per day, hours and even totals in that week.

My doubt is that I would like to be able to change the number of the weeks automatically but that I respect when returning to previous weeks the schedules with their letters.

For example

If I have a total of 30 weeks

If I write 4 then I would see the classes of Week 4 (for example: 5h Pablo, 4h Fernando, 13h Cristina)

If I write 6 then I would see the classes of Week 6 (for example: 3h Fernando, 3h Cristina)

Can anybody help me?

Thank you.TABLA HORARIOS.xlsx

I have the following Table, it is to organize private classes.

Classify the students by Name (by colors I did not know how to achieve it) and € / Hours

I have the other table that is Week and where depending on the time I indicate the name and knowing the price (€ / h), it tells me some values per day, hours and even totals in that week.

My doubt is that I would like to be able to change the number of the weeks automatically but that I respect when returning to previous weeks the schedules with their letters.

For example

If I have a total of 30 weeks

If I write 4 then I would see the classes of Week 4 (for example: 5h Pablo, 4h Fernando, 13h Cristina)

If I write 6 then I would see the classes of Week 6 (for example: 3h Fernando, 3h Cristina)

Can anybody help me?

Thank you.TABLA HORARIOS.xlsx

Hi Guys, long story short it’s been a long time since I’ve had to use a string of IF statements (probably college). I’ve had an unsuccessful crack at the below but think I just need to brush up on my excel skills in general. In the mean time I would be really grateful if someone might be able to help.

Essentially, what I want is for the below model to:

$F$5/4 & IF(H2>$E$4, H4=0, "") - If current date (feeding from H2) is greater than the lease expiry date (E4) then the value of the subject cell(H4-AA4) must equal zero.

$F$5/4 & IF(H2>$D$4, H4=0, "") - If current date (feeding from H2) is greater than the lease break date (D4) then the value of the subject cell(H4-AA4) must equal zero.

$F$5/4 & IF(H2=0, "VOID", "") - If the subject cell equals 0 then display "VOID" if not then no effect.

& IF(D4:G4="VOID", $F$5/4, "") If there are (# per G4's input) x "VOID" periods preceding the subject cell, then the subject cell should revert to $F$5/4 if not then no effect.

I’m not sure if this is even possible, thanks in advance.

hello

im new to excel

what i want to do is to built a model that predicts football scores using Poisson Dist. elo ratings etc

where can i learn how to built this thing?

thank you

]]>im new to excel

what i want to do is to built a model that predicts football scores using Poisson Dist. elo ratings etc

where can i learn how to built this thing?

thank you

Here is the formula

TODAY()>DATE(2019,6,19)

I want the contents of cell to change colour

I know it's true

Conditional formatting does not work....why???

Please help

Excel 2003

]]>TODAY()>DATE(2019,6,19)

I want the contents of cell to change colour

I know it's true

Conditional formatting does not work....why???

Please help

Excel 2003

I put this code into Conditional Formatting

Date(2019,6,19) < TODAY()

and if true change font colour....it is true and the contents of the cell should change colour

I put this same code into a cell and it came back TRUE

Why doesn't it work???

I tried rewriting the code as follows:

TODAY() > DATE(2019,6,19) and it worked and then it didn't and does not work now

PLEASE HELP!!! SO frustrating

Excel 2003

Thanks

]]>Date(2019,6,19) < TODAY()

and if true change font colour....it is true and the contents of the cell should change colour

I put this same code into a cell and it came back TRUE

Why doesn't it work???

I tried rewriting the code as follows:

TODAY() > DATE(2019,6,19) and it worked and then it didn't and does not work now

PLEASE HELP!!! SO frustrating

Excel 2003

Thanks

M | N | O | P | Q | R | S |

I put staff no. in here |
I have a formula in here when it generates sales person name after entering her staff no. in column M - formula is =INDEX('Staff info'!$a$3:$a$22,match($Q2,'Staff Info'!$G$3:$G$22,0)) |
Formula in here for single sales =IF(G34="YES,25%,20%)*12 |
I have this as where I enter Y OR N if split commission | Same as M | Same as N | Split amount goes |

So what I would like to do is:

1. if I put Y in column P I would like the figure in column O divided by 2 and this amount put into column O & S

2. If I put N in column P then the whole figure would go in column O

I hope this makes sense

Thank you in advance

So what I would like to do is:

1. if I put Y in column P I would like the figure in column O divided by 2 and this amount put into column O & S

2. If I put N in column P then the whole figure would go in column O

I hope this makes sense

Thank you in advance

Greetings from Toronto Canada :wave:

I have a table of tickets (problem mgmt) and I'm challenged to construct a arrival / closure / backlog chart to show how the organization is doing, at large, in keeping up with arrival rates.

The combo chart will be 2 bars (arrival & closure) on the primary axis along with a line for backlog on secondary axis. The horizontal axis is the last 13 months of operation. Manually created, it looks like this:

I've been using cube functions successfully to correctly calculate the arrival and closure counts for each month, but I am really challenged to find the "secret" sauce syntax to support the backlog count.

I've been trying to use date ranges within a CUBEMEMBER function call, but I keep getting '**#N/A**' on the following syntax:

I've been scouring the web for solid examples of CUBE functions ... and unless I'm looking in the wrong places, the material is really (really) thin and I haven't had much success in finding good sources.

If anyone has some handy references to CUBE functions and date range usage, I'd be very appreciative.

And of course, if I'm missing something very basic, I'd be happy to receive some comments about how to fix my syntax above.

Thank you in advance,

Lance

I have a table of tickets (problem mgmt) and I'm challenged to construct a arrival / closure / backlog chart to show how the organization is doing, at large, in keeping up with arrival rates.

The combo chart will be 2 bars (arrival & closure) on the primary axis along with a line for backlog on secondary axis. The horizontal axis is the last 13 months of operation. Manually created, it looks like this:

I've been using cube functions successfully to correctly calculate the arrival and closure counts for each month, but I am really challenged to find the "secret" sauce syntax to support the backlog count.

I've been trying to use date ranges within a CUBEMEMBER function call, but I keep getting '

=CUBEMEMBER("ThisWorkbookDataModel","[ProblemTickets].[Initial Submission Date].&[2018-01-01T00:00:00]:[ProblemTickets].[Initial Submission Date].&[2018-02-01T00:00:00]","Jan Tickets")

I've been scouring the web for solid examples of CUBE functions ... and unless I'm looking in the wrong places, the material is really (really) thin and I haven't had much success in finding good sources.

If anyone has some handy references to CUBE functions and date range usage, I'd be very appreciative.

And of course, if I'm missing something very basic, I'd be happy to receive some comments about how to fix my syntax above.

Thank you in advance,

Lance

Dears, I'm trying to achieve round up based on 0.3 multiples so that if I sum up variables, the decimal values should be round up to 0.3 multiples and remainder values returned.

Example

Response I got is 5 with no remainder value returned.

Please help out gurus in the house

]]>Example

The formular I used is not working. =ROUND(A59,0) + IF(MOD(A59,1)>=0.3,1,0) |

Please help out gurus in the house

I'm trying to add qty to Inventory (B5:G7) so that Supply meets Demand by first looking at the Storage with the lowest fruit count and adding the qty based on the lot size.

Demand is manual input

Supply is Sum(B5:B7), Sum(C5:C7)+B3 and on (cumulative)

Total is Supply - Demand

Storage table is used only to find the fruit with the lowest count. Then adding the qty based on the lot size

Apple is 4, 8, 12, 16... Orange is 5, 10, 15, 20... and so on

The result should look like this:

Again the goal is to add qty so that Total is equal or above 0, other way to think about this is to match or exceed demand

I tried to first rank the fruits in Storage table with something like

But not sure how I can add based on the lot size...

I'm looking for a solution using formula, VBA, Solver or any other combination of excel features

Or let me know if this is not possible in excel...

Thank you

Cross posts:

https://www.mrexcel.com/forum/excel-...-lot-size.html

https://chandoo.org/forum/threads/ad...ot-size.41929/

Demand is manual input

Supply is Sum(B5:B7), Sum(C5:C7)+B3 and on (cumulative)

Total is Supply - Demand

Storage table is used only to find the fruit with the lowest count. Then adding the qty based on the lot size

Apple is 4, 8, 12, 16... Orange is 5, 10, 15, 20... and so on

The result should look like this:

Again the goal is to add qty so that Total is equal or above 0, other way to think about this is to match or exceed demand

I tried to first rank the fruits in Storage table with something like

Code:

`IFERROR(INDEX(A15:A17,MATCH(MIN(C15:C17),C15:C17,0)),"")`

But not sure how I can add based on the lot size...

I'm looking for a solution using formula, VBA, Solver or any other combination of excel features

Or let me know if this is not possible in excel...

Thank you

Cross posts:

https://www.mrexcel.com/forum/excel-...-lot-size.html

https://chandoo.org/forum/threads/ad...ot-size.41929/

I am wanting to return information based on certain criteria being met.

I have used SUMPRODUCT and COUNTIF for each separate stage, but not linked them together and I have hit a brick wall.

What I am trying to achieve is,

1, Filter out the information by month (this is an annual sheet)

2, Lift how many vans a person has sold split into new and used (P-O), as in COUNTIF "BE" column K if E is not blank, sumproduct column U into summary tab - E5 - Sales Rev, repeat for column V into summary F5 - profit. But I need to discount if column O (workshop costs) is blank.

I have been doing this so far by manually filtering the new and p-o,copying onto a separate workbook, then sourcing the information. This is no longer practicable.

as this document is constantly updated I wanted to have this as an automatic summary and would like to avoid using a VBA / macro if at all possible. (these have not worked well in our workplace before).

sorry if this makes no sense.

Sample Van sales.xlsx

I have used SUMPRODUCT and COUNTIF for each separate stage, but not linked them together and I have hit a brick wall.

What I am trying to achieve is,

1, Filter out the information by month (this is an annual sheet)

2, Lift how many vans a person has sold split into new and used (P-O), as in COUNTIF "BE" column K if E is not blank, sumproduct column U into summary tab - E5 - Sales Rev, repeat for column V into summary F5 - profit. But I need to discount if column O (workshop costs) is blank.

I have been doing this so far by manually filtering the new and p-o,copying onto a separate workbook, then sourcing the information. This is no longer practicable.

as this document is constantly updated I wanted to have this as an automatic summary and would like to avoid using a VBA / macro if at all possible. (these have not worked well in our workplace before).

sorry if this makes no sense.

Sample Van sales.xlsx

Example file attached. I am trying to count the number customers in 2017 that total for 2016+2017 fall into the threasholds in A26 to A32 (greater than or equal to the amount, but less than value above) __without adding a helper column__.

For example, at the 80k level, the formula would return three customers as customer I-K totals are >= 80k, but < 100k.

The full result would be

100k - 5

80k - 3

50k -8

36k - 0

25k - 0

12.5k - 0

1 - 1

For example, at the 80k level, the formula would return three customers as customer I-K totals are >= 80k, but < 100k.

The full result would be

100k - 5

80k - 3

50k -8

36k - 0

25k - 0

12.5k - 0

1 - 1

I am very rusty with Excel so here goes...

I am using Excel 2007

I need to be able to devide the sum of cells E, G, I, K, M, O, Q, and S by Cell D and present the resukt in Cell A.

I would like the results presented in % rounded to 2 digits to the right of the decemil point. Such as 16.666666 would display as 16.67.

I will have many rows (256 of them) and want the formula to work the same in all rows starting with A2 and giving the results for that row seperately.

I tried

but didn't work. Like I said above, I am rusty with Excell.

]]>I am using Excel 2007

I need to be able to devide the sum of cells E, G, I, K, M, O, Q, and S by Cell D and present the resukt in Cell A.

I would like the results presented in % rounded to 2 digits to the right of the decemil point. Such as 16.666666 would display as 16.67.

I will have many rows (256 of them) and want the formula to work the same in all rows starting with A2 and giving the results for that row seperately.

I tried

Code:

`=(SUM=E+G+I+K+M+O+Q+S)/D`

Hi,

I am trying to create a list of random items based on their popularity, but struggling to make it work due to the fact that it can not contain any duplicates.

It will be used to generate a picking list of about 10 items from a list with over 200 items.

=MATCH(RAND(), ) creates the list, but I have not yet found a solution to remove and replace the duplicates with a new unique number, not with the use of VBAs either.

Any help? :)

]]>I am trying to create a list of random items based on their popularity, but struggling to make it work due to the fact that it can not contain any duplicates.

It will be used to generate a picking list of about 10 items from a list with over 200 items.

=MATCH(RAND(), ) creates the list, but I have not yet found a solution to remove and replace the duplicates with a new unique number, not with the use of VBAs either.

Any help? :)

Looking for a solution that returns a date based on a "least of" two criteria. One based on Tenure & the other based on Age

Please fing enclosed a sample excel sheet

Thanks in advance

Please fing enclosed a sample excel sheet

Thanks in advance

I was trying to use sumifs with a left formula but it does not work. What can I do?

]]>M | N | O | |

39 | ABC123 | 5/29/2019 | 10 |

40 | ABC456 | 5/30/2019 | 20 |

41 | ABC789 | 5/29/2019 | 30 |

42 | DEF123 | 5/30/2019 | 10 |

43 | DEF456 | 5/29/2019 | 20 |

44 | DEF789 | 5/30/2019 | 30 |

45 | |||

46 | 5/29/2019 | 5/30/2019 | |

47 | ABC | =sumifs($O$39:$O$44,Left($M$39:$M$44,3),$M47,$N$39:$N$44,N$46) | |

48 | DEF |

5/29/2019 |