Dynamic heading, trying to combine data from multiple cells

[TheJay]

I have a heading and have been trying to combine two different pieces of data.

The formula at the moment is:

=IF(ISBLANK(C6),"",("Sample (50% Discount is "&TEXT(F4,"£0.000000")&(TEXT(F4*25%,"£0.000000")&")")))

This reads as:
Sample (50% Discount is £789.030000£197.257500)

I am trying to get it to say:
Sample (50% Discount is £789.030000) Sample 2 (25% off 50% Discount is £197.257500)

I've tried many combinations and just can't get it right, could someone please tell me what I should be adding?

Is there a way to use the values found within a cell containing text and data for calculations in others cells? If so, how is this done please?

Lastly, how can I format so that when the figure reaches over 1,000 it adds a comma in the thousands?

For example:
Sample (50% Discount is £2500.000000) Sample 2 (25% off 50% Discount is £625.000000)

Into:
Sample (50% Discount is £2,500.000000) Sample 2 (25% off 50% Discount is £625.000000)

Thanks for your help.

 

[TheJay]

I'm also trying to achieve something similar with

=IF(ISBLANK(C6),"",(C3-C3*25%))

This currently appears as £1,183.545000

I would like it to read:
£1,183.545000 for x days

Number of days is calculated in C8:

=IF(ISBLANK(C6),"",C6-C5+NOT(B7))

Days are calculated factoring in 366/365 from date specified to end of year 30/03 following (inclusive) using:

=IF(ISBLANK(C6),"",SUMPRODUCT(C3/(365+(DAY(DATE(YEAR(CHOOSE({1,2},C5,C6-B7))+(MONTH(CHOOSE({1,2},C5,C6-B7))>3),2,29))=29))*LARGE(CHOOSE({1,2,3},((C6-B7)-C5)-((C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31))),(C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31)),0),{1,2})))

 

[Peter_SSs]

Try this for the first one

=IF(C6="","","Sample (50% Discount is "&TEXT(F4,"£#,##0.000000")&") Sample 2 (24% off 50% Discount is "&TEXT(F4*25%,"£#,##0.000000")&")")

 

[TheJay]

Thank you, that worked for the one problem. How about the others?

 

[TheJay]

For example, I have tried to manipulate the cell as described as follows:

=IF(ISBLANK(C6),"",(TEXT(C3-C3*25%,"£#,##0.000000")&" for "&TEXT(C3,"days")&""))

This produces:
£1,183.545000 for 26a0424

Looks like a HEX code or something?

 

[Peter_SSs]

For example, I have tried to manipulate the cell as described as follows:

Good that you are trying it yourself.

Is this what you mean?
=IF(C6="","",TEXT(C3*75%,"£#,##0.000000")&" for "&C8&" days")

 

[TheJay]

Ah yes, thanks. That does work for the lower cells.

In relation to the higher cells, it should either say x amount for "365" or "366" days depending on how many days there are in the year for the period specified.

That's why I mentioned the formula used to calculate how many days there are in the year/s specified up to 31st March (inclusive) of the following year.

 

[TheJay]

I somehow need to combine

=IF(ISBLANK(C6),"",SUMPRODUCT(C3/(365+(DAY(DATE(YEAR(CHOOSE({1,2},C5,C6-B7))+(MONTH(CHOOSE({1,2},C5,C6-B7))>3),2,29))=29))*LARGE(CHOOSE({1,2,3},((C6-B7)-C5)-((C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31))),(C6-B7)-MIN(C6-B7,DATE(YEAR(C5)+(MONTH(C5)>3),3,31)),0),{1,2})))

into

=IF(C6="","",TEXT(C3*75%,"£#,##0.000000")&" for "&&" days")

where you inserted cell reference to C8 previously.

 

[TheJay]

Code modified slightly to incorporate more wording at the beginning, still attempting to combine as described previously. Also, cells have been moved.

=IF(D7="",,"Charge is "&TEXT(D4*75%,"£#,##0.000000")&" for "&&" days")

Number of days:

=IF(ISBLANK(D7),"",D7-D6+NOT(C8))

Charge for period (calculates 365 or 366 days within financial year concerned up to and including 31/03 the following year):

=IF(ISBLANK(D7),"",SUMPRODUCT(D4/(365+(DAY(DATE(YEAR(CHOOSE({1,2},D6,D7-C8))+(MONTH(CHOOSE({1,2},D6,D7-C8))>3),2,29))=29))*LARGE(CHOOSE({1,2,3},((D7-C8)-D6)-((D7-C8)-MIN(D7-C8,DATE(YEAR(D6)+(MONTH(D6)>3),3,31))),(D7-C8)-MIN(D7-C8,DATE(YEAR(D6)+(MONTH(D6)>3),3,31)),0),{1,2})))

Start date in D6
End date in D7

 

[TheJay]

I haven't been able to figure this out.