Edit for percentage in formula please :)

[Grammarjunkie]

Hey, guys. Thanks for stopping.
I was hoping someone could tell me what I need to do to fix this formula so that it pulls the VALUE portions in a 3 decimal format. Fields G9,D10, and J24 will all have percentages in them in three decimal places. When I'm CONCATENATE-ing, I'd like the value from those cells to be in 3 decimal places format, rather than what it's currently doing, which is 0.000XXX.

I'm not sure why I have a gap in knowledge on how to make this happen.
Thank you very much for any help.

=IF(J24="","",IF(G9="","",IF(D10="","",IF(AND(J24<>G9,G9=D10),CONCATENATE("Closing Disclosure Interest Rate (",VALUE(J24),") does not match Note or ELC Interest Rate (",VALUE(G9),")"),IF(AND(D10<>J24,J24=G9),CONCATENATE("ELC interest Rate (",VALUE(D10),") does not match Closing Disclosure or Note Interest Rate (",VALUE(G9),")",IF(AND(D10=J24,G9<>D10),CONCATENATE("Note Interest Rate (",VALUE(G9),") does not match Closing Disclosure or ELC Interest Rate (",VALUE(D10),")"),IF(AND(D10<>J24,D10<>G9,J24<>G9),CONCATENATE("Note Interest Rate (",VALUE(G9),"), ELC Interest Rate (",VALUE(D10),"), and Closing Disclosure Interest Rate (",VALUE(J24),")all do not match"),""))))))))

 

[Grammarjunkie]

Hey, guys. Thanks for stopping.
I was hoping someone could tell me what I need to do to fix this formula so that it pulls the VALUE portions in a 3 decimal format. Fields G9,D10, and J24 will all have percentages in them in three decimal places. When I'm CONCATENATE-ing, I'd like the value from those cells to be in 3 decimal places format, rather than what it's currently doing, which is 0.000XXX.

I'm not sure why I have a gap in knowledge on how to make this happen.
Thank you very much for any help.

=IF(J24="","",IF(G9="","",IF(D10="","",IF(AND(J24<>G9,G9=D10),CONCATENATE("Closing Disclosure Interest Rate (",VALUE(J24),") does not match Note or ELC Interest Rate (",VALUE(G9),")"),IF(AND(D10<>J24,J24=G9),CONCATENATE("ELC interest Rate (",VALUE(D10),") does not match Closing Disclosure or Note Interest Rate (",VALUE(G9),")",IF(AND(D10=J24,G9<>D10),CONCATENATE("Note Interest Rate (",VALUE(G9),") does not match Closing Disclosure or ELC Interest Rate (",VALUE(D10),")"),IF(AND(D10<>J24,D10<>G9,J24<>G9),CONCATENATE("Note Interest Rate (",VALUE(G9),"), ELC Interest Rate (",VALUE(D10),"), and Closing Disclosure Interest Rate (",VALUE(J24),")all do not match"),""))))))))

Is there a way to do it with TEXT() instead of VALUE()? TEXT(J24,"0.000%")? Something like that?

 

[BGY23]

Hi,

Where you have ,value(j24), you need to have ,round(value(j24),3),

You need to do this for all your values. It will round the decimal to 3 places.

Probably an easier way is to put the round(cell address,3) in the formulas in D G and J. Or limit the input to 3 decimal places

 

[Grammarjunkie]

Hi,

Where you have ,value(j24), you need to have ,round(value(j24),3),

You need to do this for all your values. It will round the decimal to 3 places.

Probably an easier way is to put the round(cell address,3) in the formulas in D G and J. Or limit the input to 3 decimal places

Sorry, maybe I'm misunderstanding you. But what I'm getting is that you're suggesting to use ROUND(J24,3). However, I tried that originally, and all it does is cut off the last few decimal places. So instead of reading 0.03562, it now reads 0.03.

Edit: Sorry, opposite. Instead of reading 0.03562, it reads 0.035. I want it to read 3.562 like how it's in the field.

 

[BGY23]

Ok so round(value(j24)*100,3) etc

 

[BGY23]

Or if you show it as text then
CONCATENATE("Closing Disclosure Interest Rate (",TEXT(J24,"0.000%"),")

The .000 will limit the result to 3 decimal places and the % will show it how you want to see it.

 

[Grammarjunkie]

Ok so round(value(j24)*100,3) etc

Oh that makes sense. haha. Math was never my forte. I don't think I would have ever come to the idea of multiplying it in order to get what I wanted on my own. So I'm glad I came here.

Thank you so much!

 

[BGY23]

if D and G don't match you get a false? Is that what you wanted

 

[Grammarjunkie]

Or if you show it as text then
CONCATENATE("Closing Disclosure Interest Rate (",TEXT(J24,"0.000%"),")

The .000 will limit the result to 3 decimal places and the % will show it how you want to see it.

Oh dude! I suggested that a few posts ago! haha. I wish I had tried it. I would have been so proud of myself. I just didn't think the 0s would work in that way.

 

[Grammarjunkie]

if D and G don't match you get a false? Is that what you wanted

Hmm. No. The last string is if D10<>J2,D10<>G9,G9<>J24 don't match..

I'll have to look into that. I'm not sure why it's giving me a false.