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!!!

 

[saboh12617]

Hello, is this formula returning the expected result ? I think its way easier than imbricated ifs

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

Basically it finds the maximum of your list (the match/max function) and returns its index (n° in list), and then based on this index you find the "Gi" text and the corresponding value

 

[saboh12617]

To better understand the formula construction:

 

[Jyggalag]

Hello, is this formula returning the expected result ? I think its way easier than imbricated ifs

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

Basically it finds the maximum of your list (the match/max function) and returns its index (n° in list), and then based on this index you find the "Gi" text and the corresponding value

Hi Saboh,

Thank you very much for your input! Unfortunately, the formula does not work (also, column K-N should be ignored)

 

[saboh12617]

Nevermind, you are missing " around Total grant is bigger, last line of the formula. Excuse me can you fix it and try again ?
And if column K,L,M,N are ignored, replace F4:L4 by F4:J4 and F3:L3 by F3:J3 in formula, so :

=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";
"total grant is bigger")

 

[Jyggalag]

Nevermind, you are missing " around Total grant is bigger, last line of the formula. Excuse me can you fix it and try again ?
And if column K,L,M,N are ignored, replace F4:L4 by F4:J4 and F3:L3 by F3:J3 in formula, so :

=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";
"total grant is bigger")

Hi! The formula does not incur an error now, but it does not seem to work, because it says that my G4 is bigger than the total grant (which it is not, 77.700.000 is the largest number when compared to all G0, G1, G2, G3 and G4 numbers). Is also does not say the amount:

it is a difficult formula, sorry if i misstated something, i hope my initial message was clear? appreciating the assistance a lot!

 

[Jyggalag]

Essentially I would like to just expand this 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"; ""))

if that makes sense? i am already covering G5 and G4, but would like to cover G0-G3 as well

 

[RoryA]

Which version(s) does it need to work in?

 

[Jyggalag]

Which version(s) does it need to work in?

Hi Rory,

I am not sure what you mean by version, but I want to expand it so it does what it already does, but also cell H4, G4, F4 and E4 (right now it only doe sit for I4 and J4) if that makes sense?

so essentially check if they are higher in value than P4, and if yes, by what amount

 

[RoryA]

I mean which version(s) of Office? (you have two in your profile, and they support different functions)