Hey guys,

I'm looking for a way to check if the numbers are almost identical and return TRUE/FALSE. Due to rounding differences and other calculations before the values go off by 1 or 2 in either direction. So in the picture in row 2;3;4 you can see the numbers each year are practically the same, but in other rows the numbers may differ by for example a few hundred.

So I'm looking for a way (formula) to check row 2 and determine that its the same number (taking that small deviation in account) and return TRUE - yes, the number is the same in every year. OR return FALSE in case the numbers are different (each year something different by a bigger margin). I'm doing this because I need to separate the rows which should be the same every year from rows which are different.

excel.jpg

]]>I'm looking for a way to check if the numbers are almost identical and return TRUE/FALSE. Due to rounding differences and other calculations before the values go off by 1 or 2 in either direction. So in the picture in row 2;3;4 you can see the numbers each year are practically the same, but in other rows the numbers may differ by for example a few hundred.

So I'm looking for a way (formula) to check row 2 and determine that its the same number (taking that small deviation in account) and return TRUE - yes, the number is the same in every year. OR return FALSE in case the numbers are different (each year something different by a bigger margin). I'm doing this because I need to separate the rows which should be the same every year from rows which are different.

excel.jpg

Hi

I have a spreadsheet with information regarding quotes that we do, and these quotes are done by 4 different people

I want to be able to analyse who does the most quotes per month.

So for example in february and march, we have NB, JB, PL and AH quoting, i want to be able to count how many quotes are done by each one per month

hope this helps

thanks

]]>I have a spreadsheet with information regarding quotes that we do, and these quotes are done by 4 different people

I want to be able to analyse who does the most quotes per month.

So for example in february and march, we have NB, JB, PL and AH quoting, i want to be able to count how many quotes are done by each one per month

hope this helps

thanks

I have a %'s in cells D6:D18 - I want a formula which finds the highest value and then presents the content in the adjacent column A

I then need to do the same for the next highest and 3rd highest

any suggestions most welcome?

]]>I then need to do the same for the next highest and 3rd highest

any suggestions most welcome?

Hello

I want to make a sheet with 52 sheets (1 for every week) but I don't want to have hyperlink buttons or use the tabs to navigate.

Is it possible to have a dropdownlist of all the sheets and and a "Go to" button on a "Meny" page?

I also want the sheet to keep count on what week we are in. Is that possible to do without much problem?

(I preffer all this to be done without macros because og low programming skills)

- m3gney

]]>I want to make a sheet with 52 sheets (1 for every week) but I don't want to have hyperlink buttons or use the tabs to navigate.

Is it possible to have a dropdownlist of all the sheets and and a "Go to" button on a "Meny" page?

I also want the sheet to keep count on what week we are in. Is that possible to do without much problem?

(I preffer all this to be done without macros because og low programming skills)

- m3gney

Hello

how do i add a unicode formula into a formula?

See attached example.

Thanks...

how do i add a unicode formula into a formula?

See attached example.

Thanks...

please could somebody help make this formula work with blank cells in the range.

I understand its a syntax error but when I change it for other options it doesn't count properly using my dropdown boxes - it currently works as it is but only for the range with data in.

=IF(O14=G2,SUMPRODUCT((B3:B72280+C3:C72280>=N8+O8)+0*(B3:B72280+C3:C72280<=N9+O9),1*(D3:D72280=O11),1*(J3:J72280=O12),G3:G72280),IF(O14=H2,SUMPRODUCT((B3:B72280+C3:C72280>=N8+O8)+0*(B3:B72280+C3:C72280<=N9+O9),1*(D3:D72280=O11),1*(J3:J72280=O12),H3:H72280),IF(O14=I2,SUMPRODUCT((B3:B72280+C3:C72280>=N8+O8)+0*(B3:B72280+C3:C72280<=N9+O9),1*(D3:D72280=O11),1*(J3:J72280=O12),I3:I72280))))

Thanks in advanced

]]>I understand its a syntax error but when I change it for other options it doesn't count properly using my dropdown boxes - it currently works as it is but only for the range with data in.

=IF(O14=G2,SUMPRODUCT((B3:B72280+C3:C72280>=N8+O8)+0*(B3:B72280+C3:C72280<=N9+O9),1*(D3:D72280=O11),1*(J3:J72280=O12),G3:G72280),IF(O14=H2,SUMPRODUCT((B3:B72280+C3:C72280>=N8+O8)+0*(B3:B72280+C3:C72280<=N9+O9),1*(D3:D72280=O11),1*(J3:J72280=O12),H3:H72280),IF(O14=I2,SUMPRODUCT((B3:B72280+C3:C72280>=N8+O8)+0*(B3:B72280+C3:C72280<=N9+O9),1*(D3:D72280=O11),1*(J3:J72280=O12),I3:I72280))))

Thanks in advanced

Hi Experts,

I have a formula like this:

=IFERROR(VLOOKUP(B4,INDIRECT("'"&$C$2&"'!"&"B28:H77"),2,0),0)+IFERROR(VLOOKUP(B4,INDIRECT("'"&$D$2&"'!"&"B28:H77"),2,0),0)+IFERROR(VLOOKUP(B4,INDIRECT("'"&$E$2&"'!"&"B28:H77"),2,0),0)+IFERROR(VLOOKUP(B4,INDIRECT("'"&$F$2&"'!"&"B28:H77"),2,0),0)

Output

=10+20+0+30

more than Zero value =3

Here I want the formula to calculate the average by which gives the value more than 0

i.e 60/3=20 IF four no found more than zero 60/4

Thanks in advance..!

I have a formula like this:

=IFERROR(VLOOKUP(B4,INDIRECT("'"&$C$2&"'!"&"B28:H77"),2,0),0)+IFERROR(VLOOKUP(B4,INDIRECT("'"&$D$2&"'!"&"B28:H77"),2,0),0)+IFERROR(VLOOKUP(B4,INDIRECT("'"&$E$2&"'!"&"B28:H77"),2,0),0)+IFERROR(VLOOKUP(B4,INDIRECT("'"&$F$2&"'!"&"B28:H77"),2,0),0)

Output

=10+20+0+30

more than Zero value =3

Here I want the formula to calculate the average by which gives the value more than 0

i.e 60/3=20 IF four no found more than zero 60/4

Thanks in advance..!

Hello fellow excelers,

I was hoping someone maybe able to provide a bit of advice on a formula to calculate overtime as part of a large set of hours.

Essentially I've got a number of employees in Column A. In the subsequent columns I have every working day from the beginning of the year. In Column CA I have normal time rates, Column CB Over time rates.

So I need one column to sum all 9.5hrs or less, and the next column to sum anything above 9.5hrs but not including the 9.5hrs if possible.

I've tried SUMIF formulas to extract hours above 9.5 but I don't seem to be having any joy. I think I'm having an excel mental block.

Thanks for any help.

Luke

]]>I was hoping someone maybe able to provide a bit of advice on a formula to calculate overtime as part of a large set of hours.

Essentially I've got a number of employees in Column A. In the subsequent columns I have every working day from the beginning of the year. In Column CA I have normal time rates, Column CB Over time rates.

So I need one column to sum all 9.5hrs or less, and the next column to sum anything above 9.5hrs but not including the 9.5hrs if possible.

I've tried SUMIF formulas to extract hours above 9.5 but I don't seem to be having any joy. I think I'm having an excel mental block.

Thanks for any help.

Luke

Hi All,

I have a VLOOKUP which i'm wanting to amend but not sure how, the lookup searches through multiple sheets, but if no value is found it enters an #N/A, i was wondering if it could return 0 instead?

=VLOOKUP($C20,INDIRECT("'"&INDEX(MYSHEETS,MATCH(TRUE,COUNTIF(INDIRECT("'"&MYSHEETS&"'!C4:C100"),$C20)>0,0))&"'!C4:M100"),11,0)

]]>I have a VLOOKUP which i'm wanting to amend but not sure how, the lookup searches through multiple sheets, but if no value is found it enters an #N/A, i was wondering if it could return 0 instead?

=VLOOKUP($C20,INDIRECT("'"&INDEX(MYSHEETS,MATCH(TRUE,COUNTIF(INDIRECT("'"&MYSHEETS&"'!C4:C100"),$C20)>0,0))&"'!C4:M100"),11,0)

Evening all,

I have a table of data

I have a list of the top 5 largest numbers:

=LARGE($AM$129:$BJ$129,1)

These returned numbers are for example 531.32

A title represents that number which I'm trying to lookup and have returned.

However I seem to be unable to for some reason.

I can get the Vlookup function to work but the Hlookup won't which I'm confused about...

=HLOOKUP(AW81,AM129:BJ129,3,)

It returns #REF!

=VLOOKUP(AW81,AM94:BJ129,3)

Returns a number a few away from 531.32....

Any ideas why the Hlookup won't work the same way?

Cheers all

]]>I have a table of data

I have a list of the top 5 largest numbers:

=LARGE($AM$129:$BJ$129,1)

These returned numbers are for example 531.32

A title represents that number which I'm trying to lookup and have returned.

However I seem to be unable to for some reason.

I can get the Vlookup function to work but the Hlookup won't which I'm confused about...

=HLOOKUP(AW81,AM129:BJ129,3,)

It returns #REF!

=VLOOKUP(AW81,AM94:BJ129,3)

Returns a number a few away from 531.32....

Any ideas why the Hlookup won't work the same way?

Cheers all

Hi All

i have a list of batch numbers with numbers and a letter on the end, i then have a total run volume and i want to search for the total run volume using a batch number. My data is not in a nice organised format. i need to arrange it in the format below.

Batch Number Job Number Flow

SC320756A

SC320756B

SC320756C

SC320756D

SC320756E SC320756 2000

SC320757A

SC320757B SC320757 5000

]]>i have a list of batch numbers with numbers and a letter on the end, i then have a total run volume and i want to search for the total run volume using a batch number. My data is not in a nice organised format. i need to arrange it in the format below.

Batch Number Job Number Flow

SC320756A

SC320756B

SC320756C

SC320756D

SC320756E SC320756 2000

SC320757A

SC320757B SC320757 5000

How do I make my table created to be autofilled from my data paste tab? I am able to use vlookups but that would still require to manually type in the name and the use the vlookup function. Is there anyway I can build in the functions so that anytime I add a new row of information to the data paste tab, it will autopopulate in my table?

I can't figure out how I should set the function so that it will pull the name along with the corresponding info for that particular person.

data paste.pngteamstructure.png

]]>I can't figure out how I should set the function so that it will pull the name along with the corresponding info for that particular person.

data paste.pngteamstructure.png

Hi. Im pretty new to Excel. Could anyone help me with a solution? I need a formula for a previous or old price. When I put in a new price i'd like to see the old one.

----------A-----------B------------------C----------D----------E---------

1: [ItemsName][Cost Price]**[Previous Cost]**[Deal][Mark Up%]

2:

3:

So I'd like to change my cost price, but automatically transfer the old price into the Previous Cost Cell

----------A-----------B------------------C----------D----------E---------

1: [ItemsName][Cost Price]

2:

3:

So I'd like to change my cost price, but automatically transfer the old price into the Previous Cost Cell

Hi All,

I was hoping someone would be able to help me with a small problem,

I need a function that calculates a price based on duration,

I would like to be able to choose a value from a drop down box (this being the length of time in sec) that would have an assigned rate attached to it that would be applied to the dollar amount in the following column giving the final rate in the following column,

Example

15 sec selected in dropbox will result in the below equation

00:15 = (.06 x base rate) = Actual Cost

Below are the Duration that will be used and the rates I need attached to them

Time (selection)|Rate|Base rate|Actual Cost

5 | 0.3 | $xxx | $xxx

10 | 0.5 | $xxx | $xxx

15 | 0.60 | $xxx | $xxx

30 | 1.00 | $xxx | $xxx

45 | 1.60 | $xxx | $xxx

60 | 2.00 | $xxx | $xxx

Image attached shows the above a bit clearer (2nd image)

Its a bit confusing but any help would be greatly appreciated

Thanks

I was hoping someone would be able to help me with a small problem,

I need a function that calculates a price based on duration,

I would like to be able to choose a value from a drop down box (this being the length of time in sec) that would have an assigned rate attached to it that would be applied to the dollar amount in the following column giving the final rate in the following column,

Example

15 sec selected in dropbox will result in the below equation

00:15 = (.06 x base rate) = Actual Cost

Below are the Duration that will be used and the rates I need attached to them

Time (selection)|Rate|Base rate|Actual Cost

5 | 0.3 | $xxx | $xxx

10 | 0.5 | $xxx | $xxx

15 | 0.60 | $xxx | $xxx

30 | 1.00 | $xxx | $xxx

45 | 1.60 | $xxx | $xxx

60 | 2.00 | $xxx | $xxx

Image attached shows the above a bit clearer (2nd image)

Its a bit confusing but any help would be greatly appreciated

Thanks

Hi Everyone,

I have this conditional formatting the highlighted cell when detected multiple data and it works fine. But i wanted to add more conditioned that will only highlighted when the other column has a different data.

I attach sample excel file for more details.

Thanks.

I have this conditional formatting the highlighted cell when detected multiple data and it works fine. But i wanted to add more conditioned that will only highlighted when the other column has a different data.

I attach sample excel file for more details.

Thanks.