Help me expand my formula please :(

[Jyggalag]

Hi all,

I have this formula:

Formula: =IF(J4=""; IF(I4>P4; "G4 grant bigger than Total grant (IA) by " & TEXT(ROUND(I4-P4; 2); "0.00") & " DKK"; ""); IF(J4>P4; "G5 grant bigger than Total grant (IA) by " & TEXT(ROUND(J4-P4; 2); "0.00") & " DKK"; ""))

I want to expand it, so that it looks my TPC in column H (cell H4) if cell I4 is blank, in G4 is cell H4 is blank and in cell F4 if G4 is blank. I want it to make the same calculation and say for example if H4 > P4, "G3 grant bigger than Total grant (IA) by " & TEXT(ROUND(H4-P4; 2);"0.00") & "DKK";"").

I tried to expand the formula myself, and I have been able to do it for J4 and I4, but it keeps bugging now and telling me that I am missing a parenthesis or something, and even when I add it, it just repeats the same error over and over.

I have tried for an hour now, but am unable to make the formula function. Do we have any hidden excel geniuses here that may be able to either edit it or give me a better formula? Would be much appreciated!!!

 

[RoryA]

Ok so what was your post #6 about?

 

[Jyggalag]

Ok so what was your post #6 about?

oh that was regarding the other guy who had made a new formula in an attempt to assist me, but unfortunately, when I copy pasted his formula to replace my own, it did not work as it simply said "total grant is bigger than", where it should leave a blank cell for row 4 and it should only return a statement in row 9, where the columns for G0-G5 has a higher number

 

[RoryA]

So just change the last part of the formula to “”

 

[Jyggalag]

oh that was regarding the other guy who had made a new formula in an attempt to assist me, but unfortunately, when I copy pasted his formula to replace my own, it did not work as it simply said "total grant is bigger than", where it should leave a blank cell for row 4 and it should only return a statement in row 9, where the G column has a higher number

So just change the last part of the formula to “”

Hi Rory,

This actually worked, thank you so much!

However, I am facing the issue that if my G2 for example is loewr than the total grant, but my G1 is higher, it ignores the condition in which it should not be looking at the G1, if the G2 has data (see below):

Formula is:
=IF(INDEX(F4:J4;1;MATCH(MAX(F4:J4);F4:J4))>P4;
RIGHT(INDEX(F3:J3;1;MATCH(MAX(F4:J4);F4:J4));2) & " grant is bigger than total grant (IA) by " & INDEX(F4:J4;1;MATCH(MAX(F4:J4);F4:J4))-P4 & " DKK";
"")

Do you know how to fix the formula?

 

[RoryA]

Since you have 365:

=LET(mpos;MATCH(1E100;F4:J4);
mval;INDEX(F4:J4;mpos);
IF(mval>P4;
RIGHT(INDEX(F3:J3;mpos);2) & " grant is bigger than total grant (IA) by " & mval-P4 & " DKK";
""))

 

[saboh12617]

Hum, maybe it is due to new office 365 array formulas. Try this new "let" function instead, much cleaner but same principle.

=LET(maxVal;MAX(F4:I4);colIndex;MATCH(maxVal;F4:I4);rowName;RIGHT(INDEX($F$3:$I$3;1;colIndex);2);
IF(maxVal>T4;
rowName & " grant is bigger than total grant (IA) by ";
"total grant is bigger by ") & TEXT(ABS(maxVal-T4);"# ##") & " DDK")

 

[RoryA]

Hum, maybe it is due to new office 365 array formulas

No, it's because the largest value is not necessarily the last one, as shown in the picture posted in #24.

 

[saboh12617]

Hum, maybe it is due to new office 365 array formulas. Try this new "let" function instead, much cleaner but same principle.

=LET(maxVal;MAX(F4:I4);colIndex;MATCH(maxVal;F4:I4);rowName;RIGHT(INDEX($F$3:$I$3;1;colIndex);2);
IF(maxVal>T4;
rowName & " grant is bigger than total grant (IA) by ";
"total grant is bigger by ") & TEXT(ABS(maxVal-T4);"# ##") & " DDK")

Thanks to RoryA feedback, i remark it should be

=LET(maxVal;MAX(F4:I4);colIndex;MATCH(maxVal;F4:I4;0);rowName;RIGHT(INDEX($F$3:$I$3;1;colIndex);2);
IF(maxVal>T4;
rowName & " grant is bigger than total grant (IA) by ";
"total grant is bigger by ") & TEXT(ABS(maxVal-T4);"0") & " DDK")

As match needs to be exact.

 

[RoryA]

You are still looking up the largest value, and that is not what is wanted. The formula needs to use the last value in the row, which may not be the largest.

 

[saboh12617]

Oh nevermind i misunderstood the OP. Then all my messages should be ignored/deleted.