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]

Then all my messages should be ignored/deleted

Not at all. Based on all the examples posted up until recently, your approach was perfectly valid. Also, it may well help someone else in future who does want the maximum value regardless of where it is.

 

[Jyggalag]

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";
""))

This formula works, thank you so much Rory! You are a godsend

If I may have one final request, is there a way to edit the formula, so the number it shows has 0 decimals?

At the moment is shows around 6 decimals:

 

[RoryA]

Yes:

=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 " & ROUND(mval-P4;0) & " DKK";
""))

If you want to add other formatting, you'll need to use the TEXT function and whatever your regional formatting codes are.

 

[Jyggalag]

Yes:

=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 " & ROUND(mval-P4;0) & " DKK";
""))

If you want to add other formatting, you'll need to use the TEXT function and whatever your regional formatting codes are.

Amazing, this worked!!!

Thank you so much RoryA! You are a lifesaver

also @saboh12617 your input is much appreciated!

Here's my full formula for reference (I added text and iferror to it:

=IFERROR(LET(mpos;MATCH(1E+100;F4:J4);
mval;INDEX(F4:J4;mpos);
IF(mval>P4;
RIGHT(INDEX($F$3:$J$3;mpos);2) & " grant is bigger than total grant (IA) by " & TEXT(ROUND(mval-P4;0);"#.##0") & " DKK";
""));"")