Hi There,

I have gone through your site and found good resources for refreshing my knowledge of Excel. However I am still stuck in getting the solution of counting the unique string values from the filtered Table.

Please note I am using Office version 2013 and filtering table based on Department.

Can you please help me in this regards. The file is attached for your kind perusal.

Many thank you in advance.

Best Regards,

Farooq

I have gone through your site and found good resources for refreshing my knowledge of Excel. However I am still stuck in getting the solution of counting the unique string values from the filtered Table.

Please note I am using Office version 2013 and filtering table based on Department.

Can you please help me in this regards. The file is attached for your kind perusal.

Many thank you in advance.

Best Regards,

Farooq

I wonder if anyone can help.

I have a sheet where I wish to enter the letter "F" in the column "fav" aligned to the horse that has the shortest starting price (SP).

For example in the attached sheet in the first race (R1) number 6. Fire N Ice is the favourite with the shortest price of 3.29, in race 2 it would be gravitational with an SP of 2.29. A formula would then enable me to auto populate the fav column.

Thanks in advance

Wayne

I have a sheet where I wish to enter the letter "F" in the column "fav" aligned to the horse that has the shortest starting price (SP).

For example in the attached sheet in the first race (R1) number 6. Fire N Ice is the favourite with the shortest price of 3.29, in race 2 it would be gravitational with an SP of 2.29. A formula would then enable me to auto populate the fav column.

Thanks in advance

Wayne

Hi fellow Excelers,

Just so that you have more information on what I am currently inputting see attached image.

The formula is in Cell N9.

Say for example the BLUE CELL (dynamic dropdown list) selected is EBY PROMO, if this is the case the FINAL SALE VALUE must be multiplied by the lookup value N24 15%. However, if this calculation meant the value is greater than £1.00 then I would like the PLATFORM FEE to remain at MAX £1.00. If for example the calculation retrieved less than £1.00 (i.e. £0.75) then I would like the lower value to be set.

If for example the BLUE CELL was EBY (this is a dropdown list) then the lookup value for the calculation is set to 9% however for this Selected platform I DO NOT want the £1.00 condition applied. If the calculation is >£1.00 then that's fine. This condition I want only for EBY PROMO.

I'd be grateful if anyone can help on this formula

Rahul

Just so that you have more information on what I am currently inputting see attached image.

The formula is in Cell N9.

Say for example the BLUE CELL (dynamic dropdown list) selected is EBY PROMO, if this is the case the FINAL SALE VALUE must be multiplied by the lookup value N24 15%. However, if this calculation meant the value is greater than £1.00 then I would like the PLATFORM FEE to remain at MAX £1.00. If for example the calculation retrieved less than £1.00 (i.e. £0.75) then I would like the lower value to be set.

If for example the BLUE CELL was EBY (this is a dropdown list) then the lookup value for the calculation is set to 9% however for this Selected platform I DO NOT want the £1.00 condition applied. If the calculation is >£1.00 then that's fine. This condition I want only for EBY PROMO.

I'd be grateful if anyone can help on this formula

Rahul

Hi dear members,

I would appreciate your kind help on the below comparison formula.

I have a comparison formula set like this in cell H5 - red circled below- which is showing "N/A" since there are no values in bw cells B5 and G5. This is ok!

But I have another foruma in cell E5 - red circled below - where I refer to this previous cell "N/A".

The formula here should check if the values is in between the given number ranges and should return an error as the referred cell is an "N/A" therefore it does not fall into any number ranges. But this returns a 5 as default and I do not understand why. If I put a number in the first chart and the forumula returns not N/A but a number, then this forumula in the second screenshot works perfectly.

I would like this second formula's cell - E5 to return "N/A" if the referred cell - H5 - has "N/A" in it. Any ideas how to do that?

Many thanks!

I would appreciate your kind help on the below comparison formula.

I have a comparison formula set like this in cell H5 - red circled below- which is showing "N/A" since there are no values in bw cells B5 and G5. This is ok!

But I have another foruma in cell E5 - red circled below - where I refer to this previous cell "N/A".

The formula here should check if the values is in between the given number ranges and should return an error as the referred cell is an "N/A" therefore it does not fall into any number ranges. But this returns a 5 as default and I do not understand why. If I put a number in the first chart and the forumula returns not N/A but a number, then this forumula in the second screenshot works perfectly.

I would like this second formula's cell - E5 to return "N/A" if the referred cell - H5 - has "N/A" in it. Any ideas how to do that?

Many thanks!

I have three large datasets on three separate tabs. I need to combine one data point from each tab inside a formula so that the formula sees these as an array. So, is there a way to create an array from cells that are not next to each other on the sheet?

I need to do this over and over about a 1000 times, hence formula better than manual.

Thank you,

Phil

]]>I need to do this over and over about a 1000 times, hence formula better than manual.

Thank you,

Phil

Hi all

Having a brain fade, I want to show in a cell a result that is rounded up to nearest dollar value

Cell E1 has value of 397.00

in cell F1 I want to multiply cell E1 x 4%, but have the answer rounded up to the nearest dollar value

example E1 397.00

F1 412.88 ( but to show 413.00)

Any help much appreicated

]]>Having a brain fade, I want to show in a cell a result that is rounded up to nearest dollar value

Cell E1 has value of 397.00

in cell F1 I want to multiply cell E1 x 4%, but have the answer rounded up to the nearest dollar value

example E1 397.00

F1 412.88 ( but to show 413.00)

Any help much appreicated

Okay, this is killing me!

I have two workbooks, Banks and Supervisor. I want to be able to take information from Banks and calculate it into Supervisor. I have time added up in Banks, [hh]:mm custom format in Column L. Column P is the Activity Code. Column B is the date.

In Supervisor, I want to look for any date that falls within the month (in this case, July - found in X6 and W6), and has an activity code of 1. With those found, sum up the time spent and put it in cell B6 of Supervisor. I know SUMIFS won't work if Banks is closed, so I have to use SUMPRODUCT. I know I've done this before, but for the life of me, I can't get it to work. It always shows up as #VALUE!. Everything is formatted as Custom: [hh]:mm

=SUMPRODUCT(([Banks.xlsx]Log!$B$11:$B$1999<=X6)*([Banks.xlsx]Log!$B$11:$B$1999>=W6)*([Banks.xlsx]Log!$P$11:$P$1999=1)*[Banks.xlsx]Log!$L$11:$L$1999)

**Note: Both sheets are Restricted. However, I have full access to both.**

I have two workbooks, Banks and Supervisor. I want to be able to take information from Banks and calculate it into Supervisor. I have time added up in Banks, [hh]:mm custom format in Column L. Column P is the Activity Code. Column B is the date.

In Supervisor, I want to look for any date that falls within the month (in this case, July - found in X6 and W6), and has an activity code of 1. With those found, sum up the time spent and put it in cell B6 of Supervisor. I know SUMIFS won't work if Banks is closed, so I have to use SUMPRODUCT. I know I've done this before, but for the life of me, I can't get it to work. It always shows up as #VALUE!. Everything is formatted as Custom: [hh]:mm

=SUMPRODUCT(([Banks.xlsx]Log!$B$11:$B$1999<=X6)*([Banks.xlsx]Log!$B$11:$B$1999>=W6)*([Banks.xlsx]Log!$P$11:$P$1999=1)*[Banks.xlsx]Log!$L$11:$L$1999)

**Note: Both sheets are Restricted. However, I have full access to both.**

Hello All,

Can somebody look at my code and advise what is wrong with my code?

Regards,

Taha

Can somebody look at my code and advise what is wrong with my code?

Regards,

Taha

I'm beginning to think this can't be done, but hopefully someone here has a solution...

I scan a tag and and this number is what populates excel:**01**05000223420307**17**24501**10**1919B

from that number, the qualifiers are bold.

I need to extract the numbers between the "01" and "17" into one cell, between "17" and "10" in another, and those following the "10" in another.

Unfortunately there are two snags. The first set of numbers I need are either 13 or 14 digits long (no real rhyme or reason). Additionally, the "17" an "10" occasionally change places.

Is this even possible?:frusty:

]]>I scan a tag and and this number is what populates excel:

from that number, the qualifiers are bold.

I need to extract the numbers between the "01" and "17" into one cell, between "17" and "10" in another, and those following the "10" in another.

Unfortunately there are two snags. The first set of numbers I need are either 13 or 14 digits long (no real rhyme or reason). Additionally, the "17" an "10" occasionally change places.

Is this even possible?:frusty:

I need a two step formula....if this cell contains a word, lookup this name in a chart and return an X instead of the Time in the cell.:frusty:

I fear this is gonna be considered a really dumb question!

Attached roster I have to produce - the worksheet "DATA" contains the base roster information.

The shift options are listed in columns Y & Z.

The input value of B3 determines the outcome of B4 and B5 and is calculated by a nested IF formula.

This is copied throughout to populate the 4 week roster.

**QUESTION 1 - **when I attempt to copy the populated roster in "DATA" to the "ROSTER" worksheet it doesn't copy through the formulas so that if I change the input in ROSTER B3 the outcome of ROSTER B4 and B5 changes as it does in those cells in "DATA"

I've tried every PASTE SPECIAL option I can without success?

QUESTION 2 -is there a simpler/tidier way to 'call' this data from columns Y & Z into the target cells in rows 4,7, 10 and 13?

... over to you gurus

Attached roster I have to produce - the worksheet "DATA" contains the base roster information.

The shift options are listed in columns Y & Z.

The input value of B3 determines the outcome of B4 and B5 and is calculated by a nested IF formula.

This is copied throughout to populate the 4 week roster.

I've tried every PASTE SPECIAL option I can without success?

QUESTION 2 -

... over to you gurus

I wonder if anyone can help.

I have a spreadsheet with 3 columns of data. I wish to add automatically to each cell in the 4th column the number of instances that the value in col 1 occur.

For instance in col 4, the number 10 is placed for all the R1s (as there are 10 instances of R1) and then the number 6 for R2.

Many thanks!!

]]>I have a spreadsheet with 3 columns of data. I wish to add automatically to each cell in the 4th column the number of instances that the value in col 1 occur.

For instance in col 4, the number 10 is placed for all the R1s (as there are 10 instances of R1) and then the number 6 for R2.

Many thanks!!

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R1 | 1100m | Mdn | 10 |

R2 | 1300m | Mdn | 6 |

R2 | 1300m | Mdn | 6 |

R2 | 1300m | Mdn | 6 |

R2 | 1300m | Mdn | 6 |

R2 | 1300m | Mdn | 6 |

R2 | 1300m | Mdn | 6 |

I need a formula to show the REF No to be inserted in Result Column when the Amt is = to the Negative Amt as shown

Hello all,

A B C D E F

L L L L L L

I Have a file which consist of an employee leave data which are in different dates. I need to replace with L1,L2,L3,L4,L5. If an employee is taking 5 leaves it need to be filled till L5. Another employee is having 3 leave then it need to be completed till L3 alone. If an employee is taking more than 5 days it should be filled with CL1, CL2.....

Kindly help on this.

Thanks in advance

]]>A B C D E F

L L L L L L

I Have a file which consist of an employee leave data which are in different dates. I need to replace with L1,L2,L3,L4,L5. If an employee is taking 5 leaves it need to be filled till L5. Another employee is having 3 leave then it need to be completed till L3 alone. If an employee is taking more than 5 days it should be filled with CL1, CL2.....

Kindly help on this.

Thanks in advance

what I want to do as the image above, is rate the values if they are between the values on the right of the table by the values on the left. so if the far right column are lets say 3.77 in will take the rating value of the (display these numbers) so it will return a value of 9 in the Column which says (display numbers go here). Do I need to use java script for this, or can it be done in a long string?