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]

... 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)...

View attachment 114432

Your description of what is happening is not consistent with that image. In the image, the cell correctly returns that the total grant is bigger (as expected).

 

[Jyggalag]

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

Ah sorry,

I have this version

 

[Jyggalag]

Your description of what is happening is not consistent with that image. In the image, the cell correctly returns that the total grant is bigger (as expected).

Yes, my formula works, but it only checks conditions based on cell J4 and I4. I want to expand it, so it also does it for H4, G4, F4 and E4

 

[RoryA]

The formula in the picture is checking F4:J4.

 

[Jyggalag]

S

Your description of what is happening is not consistent with that image. In the image, the cell correctly returns that the total grant is bigger (as expected).

Sorry if I misspoke.

So essentially, my formula already works and it does exactly what I intend for it to. However, I want to expand it beyond what it does right now, so it not only checks at first J4, then if J4 is blank, it checks I4, and if I4 is not blank, it notes the difference, if the number is higher.

What I want to do is:
Already does this:1) check J4, if J4 is not blank, it notes the difference, if the number is higher. If J4 is blank, it moves on to I4.
Already does this: 2) then check I4, if I4 is not blank, it notes the difference, if the number is higher. If I4 is blank, it moves on to H4.
Does not do this: 3) then check H4, if H4 is not blank, it notes the difference, if the number is higher. If H4 is blank, it moves on to G4.
Does not do this: 4) then check G4, if G4 is not blank, it notes the difference, if the number is higher. If G4 is blank, it moves on to F4.
Does not do this: 5) then check F4, if F4 is not blank, it notes the difference, if the number is higher. If F4 is blank, it moves on to E4.

Does this make sense?

 

[Jyggalag]

TH

The formula in the picture is checking F4:J4.

This is the formula in the picture:

As can you see, it currently does not link to E4-H4 at all

 

[RoryA]

Nope. Look at the picture that I linked to in my reply. That is not the formula.

 

[saboh12617]

I'm sorry i don't understand, with the formula i gave you i get the correct answer, it does not tell me what you said. Also it ignores the empty cells already.

=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 ";
"total grant is bigger by ")
& TEXT(ABS(INDEX(F4:J4;1;MATCH(MAX(F4:J4);F4:J4))-P4);"# ##") & " DKK"

 

[Jyggalag]

I'm sorry i don't understand, with the formula i gave you i get the correct answer, it does not tell me what you said. Also it ignores the empty cells already.

=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 ";
"total grant is bigger by ")
& TEXT(ABS(INDEX(F4:J4;1;MATCH(MAX(F4:J4);F4:J4))-P4);"# ##") & " DKK"

View attachment 114481

It returns this error for me and highlights the number "1" in the formula

 

[Jyggalag]

H

Nope. Look at the picture that I linked to in my reply. That is not the formula.

Hi Rory,

I think I am completely lost on your point, I am very sorry for this, but the ONLY formula that I have linked that is relevant in this ticket is the following formula that I am currently using:

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

This formula is currently being used here:

As you can see, it correctly highlights that cell J9 is higher than cell P9 by amount 15.967 DKK. As for the other cells from J4:P8 it correctly leaves a blank cell, as the values in column P are higher than those in either column J or I.

However, the issue that I have is that if BOTH the cells in column J and I are blank, my formula does not continue further by looking at the cells in column H, G, F and E - which is what I want it to do.

I tried to expand my formula to make it check these columns too, but it just returns "You've entered too many arguments for this function".

Which is why I made this thread in the hope that somebody else can expand on my formula

Hope that makes sense? You can disregard all my other messages, I think this message should sum up my problem and my question concisely