Hi. I have some shares which originally cost me £2000 on 27/5/14.

Today have gained £200, partly capital growth, partly dividend income.

The total return is 10%. No problem. But what is this as an 'annualised' rate of return, given that i bought them about 2.5 years ago?

Excel 2007 has several formulas for these sorts of calcs, but I get confused as so many refer to mortgage interest and debt sums.

Many thanks as always

KK

]]>Today have gained £200, partly capital growth, partly dividend income.

The total return is 10%. No problem. But what is this as an 'annualised' rate of return, given that i bought them about 2.5 years ago?

Excel 2007 has several formulas for these sorts of calcs, but I get confused as so many refer to mortgage interest and debt sums.

Many thanks as always

KK

I'm going to try and explain my request, some how!!

In column A the user will enter text like BT1, BT2, BT3

In column C a value is generated automatically

I need to have cells that sum the values in the C column based on the values of the A column,

In other words I need the summed value of C for all the entries. (Ohh this is difficult.)

If column A has 4 "BT1" I need it to sum the four values in column C that pertain to "BT1"

Excel.JPG

]]>In column A the user will enter text like BT1, BT2, BT3

In column C a value is generated automatically

I need to have cells that sum the values in the C column based on the values of the A column,

In other words I need the summed value of C for all the entries. (Ohh this is difficult.)

If column A has 4 "BT1" I need it to sum the four values in column C that pertain to "BT1"

Excel.JPG

ScreenHunter_001.jpg

B2:G9 contains 6 blocks of data with label TA, TB & TC.

B11:D16 shows the count and sum results for all the 3 labels.

What are the formulas in D11:D16 ?

B2:G9 contains 6 blocks of data with label TA, TB & TC.

B11:D16 shows the count and sum results for all the 3 labels.

What are the formulas in D11:D16 ?

Hello Friends

If the cells C4:C43 have 1 then the corresponding value in the cells B4:B43 need to be plotted in the light yellow cells E4:E40 by formulas.

thanks in advance

If the cells C4:C43 have 1 then the corresponding value in the cells B4:B43 need to be plotted in the light yellow cells E4:E40 by formulas.

thanks in advance

Hello,

I need and formula that matches and values between a set of values in one row with values in another.

Criteria

>In column "AJ": I want the value from “AI” to be match with value between “I2:AH2” but only match with the heading of “MP” with the result being the number that is in the same row as the formula.

>In column "AK": I want the value from “AI” to be match with value between “I2:AH2” but only match with the heading of “P” with the result being the number that is in the same row as the formula.

Please see the attached spreadsheet self-calculated examples what I am trying to accomplish. Everything in the attached spreadsheet is calculated and entered by hand so it may contain errors. Thank you for any help you can provide it. Please feel free to ask any questions.

I need and formula that matches and values between a set of values in one row with values in another.

Criteria

>In column "AJ": I want the value from “AI” to be match with value between “I2:AH2” but only match with the heading of “MP” with the result being the number that is in the same row as the formula.

>In column "AK": I want the value from “AI” to be match with value between “I2:AH2” but only match with the heading of “P” with the result being the number that is in the same row as the formula.

Please see the attached spreadsheet self-calculated examples what I am trying to accomplish. Everything in the attached spreadsheet is calculated and entered by hand so it may contain errors. Thank you for any help you can provide it. Please feel free to ask any questions.

Hello friends, am creating a custom function for my sheet,

Call this function mm2cm, which will convert millimeters to centimeters.

Now say the value in cell A1 is 50.00 mm and i enter =mm2cm(A1) in cell B1

The result in B1 is 5,

i want the value in B1 to show as 5.00 cm

meaning as if it has been formatted to 0.00 cm

Can anyone help me with this

My VBA Code so far is as follows

]]>Call this function mm2cm, which will convert millimeters to centimeters.

Now say the value in cell A1 is 50.00 mm and i enter =mm2cm(A1) in cell B1

The result in B1 is 5,

i want the value in B1 to show as 5.00 cm

meaning as if it has been formatted to 0.00 cm

Can anyone help me with this

My VBA Code so far is as follows

Code:

`Public Function mm2cm(number1)`

mm2cm = number1 / 10

End Function

I have this array formula that all works as intended when value to be retrieved is in column ‘S’, however the value may be in column ‘T’, how do I tweak this formula to retrieve it when it is.

(When ‘S’ = value, ‘T’ will always = ”” and when ‘S’= ””, ‘T’ will always = value)

]]>(When ‘S’ = value, ‘T’ will always = ”” and when ‘S’= ””, ‘T’ will always = value)

Code:

`= IF(SUMPRODUCT(--($R$7:$R$708=Z$839))>=ROWS(Z$842:Z844),INDEX($S$7:$S$708,SMALL(IF($R$7:$R$708=Z$839,ROW($R$7:$R$708)-ROW($S$7)+1),ROWS(Z$842:Z844))),"")`

Hi.

I have a huge data in excel like the one in the image link below:

Sample.jpg

I need to know which part of the receipt 450.00 (cell C8) belongs to D3, D4, D5 cells and etc. beginning from the first non-zero cell in D column, so that to find currency difference effect within 450.00 (C8) like the following: (200.00)*(1.80-1.98)+(450.00-200.00)*(1.86-1.98) or if we have 50.00 instead of 450.00 just to make calculation like this: (50.00)*(1.80-1.98).

I just can't imagine how to do it using formulas and I don't know VBA. So it is very much appreciated if anyone can help me with this issue either via excel formulas or macro.

]]>I have a huge data in excel like the one in the image link below:

Sample.jpg

I need to know which part of the receipt 450.00 (cell C8) belongs to D3, D4, D5 cells and etc. beginning from the first non-zero cell in D column, so that to find currency difference effect within 450.00 (C8) like the following: (200.00)*(1.80-1.98)+(450.00-200.00)*(1.86-1.98) or if we have 50.00 instead of 450.00 just to make calculation like this: (50.00)*(1.80-1.98).

I just can't imagine how to do it using formulas and I don't know VBA. So it is very much appreciated if anyone can help me with this issue either via excel formulas or macro.

HERE IS WHAT WE ARE TRYING TO ACHIEVE:

D33 needs to do show certain words given certain criteria as laid out here....

IF D22 and D13 is Yes then ....

--If D31 is greater then or equal to 12 show MAXIMUM

--If D31 is 10 or 11 then show TARGET

--If D31 is 8 or 9 then show THRESHOLD

However,

IF D22 is NO and D13 is yes then...

--If D31 is equal to 3 show MAXIMUM

--If D31 is equal to 2 show TARGET

--If D31 is equal to 1 show THRESHOLD

However,

IF D22 is YES and D13 is NO then...

--If D31 is greater then or equal to 10 show MAXIMUM

--If D31 is 8 or 9 then show TARGET

--If D31 is 6 or 7 then show THRESHOLD

D33 needs to do show certain words given certain criteria as laid out here....

IF D22 and D13 is Yes then ....

--If D31 is greater then or equal to 12 show MAXIMUM

--If D31 is 10 or 11 then show TARGET

--If D31 is 8 or 9 then show THRESHOLD

However,

IF D22 is NO and D13 is yes then...

--If D31 is equal to 3 show MAXIMUM

--If D31 is equal to 2 show TARGET

--If D31 is equal to 1 show THRESHOLD

However,

IF D22 is YES and D13 is NO then...

--If D31 is greater then or equal to 10 show MAXIMUM

--If D31 is 8 or 9 then show TARGET

--If D31 is 6 or 7 then show THRESHOLD

Hello Friends

I need to combine 2 columns from Sheet1 and Sheet2, in both sheets Column C should be combined.

But if any texts repeated then it should be plotted as is i.e., not removing duplicates.

But if any numbers repeated then it should be plotted once by removing the duplicate entires.

Now I had hardcored the expected results in the Sheet3, Column C light yellow cells.

thanks in advance

I need to combine 2 columns from Sheet1 and Sheet2, in both sheets Column C should be combined.

But if any texts repeated then it should be plotted as is i.e., not removing duplicates.

But if any numbers repeated then it should be plotted once by removing the duplicate entires.

Now I had hardcored the expected results in the Sheet3, Column C light yellow cells.

thanks in advance

G'day,

I hope I can articulate this one. Apologies, this is my first post.

I have a set of data within the range B3:K13 which includes random numbers under 10 using =RAND()*10.

I have created 4 named ranges (where A3 is blank):

Dates: A3:A13 (Numbered 1 - 10)

Dates1:A4:A13

Timesteps: B3:K3 (Dated 1/01/2016 - 10/01/2016)

Timesteps1: A3:K3

So I have 4 criteria:

Start Date:B25 (Dropdown List "Dates")

End Date: B26 (Dropdown List "Dates")

Start Timestep: B27 (Dropdown List "Timesteps")

End Timestep: B28 (Dropdown List "Timesteps")

There are two formulas I have used that can generate the answers I need.

=SUM(OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)))

=SUM(INDEX($B$4:$K$13,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0)):INDEX($B$4:$K$13,MATCH($B$26,Dates,0),MATCH($B$28,Timesteps,0)))

Everything is good so far however I want add another criteria to this formula.

I want to create a new formula that will still sum the dynamic ranges not only for the selected range (All week) but for weekends and weekdays as well within that selected range.

I tried this formula but it didn't work.

=SUM(IF($N$4:$N$13>=6,OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)),0))

I have created a work around in order to get there =SUMIFS(P4:P13,Dates,">="&$B$25,Dates,"<="&$B$26). But I need it in one formula!

I know this is a tricky one but if anyone could help me out it would be much appreciated.

I got the idea from Excelisfun's EMT 1071 - https://www.youtube.com/watch?v=lh4VjEoD5WY

Here is a screenshot of my spreadsheet - https://postimg.org/image/c455h66in/

Cheers!

]]>I hope I can articulate this one. Apologies, this is my first post.

I have a set of data within the range B3:K13 which includes random numbers under 10 using =RAND()*10.

I have created 4 named ranges (where A3 is blank):

Dates: A3:A13 (Numbered 1 - 10)

Dates1:A4:A13

Timesteps: B3:K3 (Dated 1/01/2016 - 10/01/2016)

Timesteps1: A3:K3

So I have 4 criteria:

Start Date:B25 (Dropdown List "Dates")

End Date: B26 (Dropdown List "Dates")

Start Timestep: B27 (Dropdown List "Timesteps")

End Timestep: B28 (Dropdown List "Timesteps")

There are two formulas I have used that can generate the answers I need.

=SUM(OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)))

=SUM(INDEX($B$4:$K$13,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0)):INDEX($B$4:$K$13,MATCH($B$26,Dates,0),MATCH($B$28,Timesteps,0)))

Everything is good so far however I want add another criteria to this formula.

I want to create a new formula that will still sum the dynamic ranges not only for the selected range (All week) but for weekends and weekdays as well within that selected range.

I tried this formula but it didn't work.

=SUM(IF($N$4:$N$13>=6,OFFSET($A$3,MATCH($B$25,Dates,0),MATCH($B$27,Timesteps,0),MATCH($B$26,Dates1,0)-MATCH($B$25,Dates,0),MATCH($B$28,Timesteps1,0)-MATCH($B$27,Timesteps,0)),0))

I have created a work around in order to get there =SUMIFS(P4:P13,Dates,">="&$B$25,Dates,"<="&$B$26). But I need it in one formula!

I know this is a tricky one but if anyone could help me out it would be much appreciated.

I got the idea from Excelisfun's EMT 1071 - https://www.youtube.com/watch?v=lh4VjEoD5WY

Here is a screenshot of my spreadsheet - https://postimg.org/image/c455h66in/

Cheers!

hi friends,

please help me as I have to fill data in one sheet from another sheet. I want that I put OID value in cell and all required data fill automatically.please help I am sending attachment

please help me as I have to fill data in one sheet from another sheet. I want that I put OID value in cell and all required data fill automatically.please help I am sending attachment

Hi guys,

I'm trying to do a vlookup that looks at different letter ratings (AAA,+AA,AA,AA-,A+,A,A-,etc.) and give that letter rating a numeric value from 0 to 100 that is in an array in another worksheet. But for some reason, my vlookup doesnt seem to recognize the letter rating either in the main worksheet or in the reference table with the numeric scale in it. I'm not sure whats wrong. Please help!

I've attached my 2 worksheets in the following example:

https://expirebox.com/download/eacc6...95dbbe1a1.html

Thanks for the help!

P.S. The Vlookup is in column U of the spreadsheet

Gabriel

I'm trying to do a vlookup that looks at different letter ratings (AAA,+AA,AA,AA-,A+,A,A-,etc.) and give that letter rating a numeric value from 0 to 100 that is in an array in another worksheet. But for some reason, my vlookup doesnt seem to recognize the letter rating either in the main worksheet or in the reference table with the numeric scale in it. I'm not sure whats wrong. Please help!

I've attached my 2 worksheets in the following example:

https://expirebox.com/download/eacc6...95dbbe1a1.html

Thanks for the help!

P.S. The Vlookup is in column U of the spreadsheet

Gabriel

Hello:

I need a varaible to apply in Vlookup function.

Assume I have J3 = 4

I have formula in cell E3 = VLookup(C5,Data,XX,0)

Here i need XX to be the value of J3 so formula needs to be like:

=Vlookup(C5,Data,4,0)

Please let me know if any questions.

Riz

]]>I need a varaible to apply in Vlookup function.

Assume I have J3 = 4

I have formula in cell E3 = VLookup(C5,Data,XX,0)

Here i need XX to be the value of J3 so formula needs to be like:

=Vlookup(C5,Data,4,0)

Please let me know if any questions.

Riz

Say you have a database of sponsors for children. One column is for the number of sponsors the child has. Then there are a few other columns "Sponsor #1" "address" "Sponsor #2" "address" etc.

How can you set up the "number of sponsors" cell to display the number of cells in that row (Sponsor #1, Sponsor #2, Sponsor #3, etc) that are not blank? I'm not sure how to even search for such an answer.

]]>How can you set up the "number of sponsors" cell to display the number of cells in that row (Sponsor #1, Sponsor #2, Sponsor #3, etc) that are not blank? I'm not sure how to even search for such an answer.