MID text formula

[jamilm]

Gurus,

i have a formula as text in one cell and i would like to add one more array through the mid function.


'=SUMPRODUCT(MONETARY_AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,999,998},0)),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"51005"},0)))*-1

'=SUMPRODUCT(MONETARY_AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,999,998},0)),1-(FUND="KY004"),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"51005"},0)))*-1


i know this is possible through the MID function, i somehow tried but it did not work.

grateful for your contribution.

 

[Firefly2012]

Would you mind explaining what you mean by "throught the MID function" as it isn't immediately clear to me - do you mean you want to manipulate the section of your second formula highlighted in red?

Please explain as far as possible what you want the additional formula constituent to achieve.

 

[XOR LX]

Despite your limited explanation (thanks!), am I to understand that you wish to amend your formula so that it now includes a further condition along the lines of "'entries in the "FUND" range are NOT EQUAL TO "KY004"'? Or should this be 'entries in the "FUND" range DO NOT CONTAIN the string "KY004"'?

Regards

 

[jamilm]

i apologize for not explaining it clearly. i have 1000 of rows that contain this formula and manually typing 1-(FUND="KY004") inside the existing formula will take alot of time. so i wanted to use MID or concatenate function to insert 1-(FUND="KY004") in all of my 1000 rows formulas.

Would you mind explaining what you mean by "throught the MID function" as it isn't immediately clear to me - do you mean you want to manipulate the section of your second formula highlighted in red?

Please explain as far as possible what you want the additional formula constituent to achieve.

 

[Firefly2012]

Can't you just make the adjustment to the first formula and then copy it down to the remaining cells?

 

[XOR LX]

But do you actually know how this formula works? Do you know what 1-(FUND="KY004") means? (Answer: not much on its own)

Have you looked at my earlier question? As with Firefly, I am struggling to understand what you mean by "use MID or CONCATENATE to insert...".

Regards

 

[jamilm]

But do you actually know how this formula works? Do you know what 1-(FUND="KY004") means? (Answer: not much on its own)

Have you looked at my earlier question? As with Firefly, I am struggling to understand what you mean by "use MID or CONCATENATE to insert...".

Regards

yes, i know what that means, it means that in the rangecolumn named FUND any KY004 to be excluded. there is no problem in formula itself only is a modification problem that requires the MID function.

 

[jamilm]

thanks Firefly. but the reason, i cannot copy it to down, becuase each row has a different year =SUMPRODUCT(MONETARY_AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2004},0)),
i want to someone insert this addtional 1-(FUND="KY004") array, inside of the each formula that varies by each year.

Can't you just make the adjustment to the first formula and then copy it down to the remaining cells?

 

[ukmikeb]

Hi

If all formulae have the same basic structure can't you do a blanket replace such as :-
Replace "998},0))," with "998},0)),1-(FUND="KY004"),"

hth

 

[jamilm]

this is very good suggesttion, probably can be done by substitude formula also, but question is how ?

Hi

If all formulae have the same basic structure can't you do a blanket replace such as :-
Replace "998},0))," with "998},0)),1-(FUND="KY004"),"

hth

=REPLACE(A1, "998},0))" ,,"998},0)),-1(FUND="ky004")") DID NOT WORK