=IF formula for number range = text

[KarolSweats]

I have a excel sheet that is to be displayed and automatically change daily when others are updated. I am having issues with the IF statement not working, i havent had to mess with it in a while and might be missing something but it seems to me that its just not picking it up.

i have this in the cell F8 where is says million in bold =IF(J29<=999999,"Million",IF(J29>=1000000,"Billion")) for if the cell is under 999999 it will say million and over a billion it says billion

i also have the cell D8 where is says 7.5 and need help in that too. I have =IF(J29<=999999,LEFT(J29,3),IF(J29>=1000000,LEFT(J29,1)&"."&MID(J29,2,1))) which i thought if a cell has for example 750,000,000 it would do the first if and only take the first 3 numbers and input 750, and if the number was 1,600,000,000 the second if would be used and take the first number "1" add a "." and then the third number "6" and do 1.6. When i do that no matter what number is in the cell it automatically does the 1.6 formula.

I have the excel sheet attached here and highlighted the two cells im having trouble with in red text and the cell they both reference in green if you want to look, i could really use help with understanding what i should do.

Thank you

[Sandtree]

At what point do you want it to switch from Million to Billion? Should 750,000,000 be shown as 750 Million or 0.75 Billion for example?

Do you always want to round down? so 100.8 Million shows as 101 Million or 100 Million?

[KarolSweats]

i was trying to go for if the cell j29 was anything under a billion it would say million and anything over a billion it would say billion so it would show 750,000,000 as 750 million and if it went to billion it would show 1.0 billion or so on

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Sandtree]

So I would do it like this (see attached)

The rounding I am currently doing natural rounding (ie 0.5 goes up 0.49999 goes down) but you could change to RoundDown function if you want anything less than 1 to go down (as your original formula was trying to do)

[KarolSweats]

That was alot easier fix than i figured i went way out of the way for it

thank you!

[Sandtree]

No problem (note the extra text I added above after your reply).

If you are dealing with numbers then stick with maths functions like plus, minus, divide etc and leave string functions (left, length, right etc) for text

You technically may even be able to do it with formatting but I am not good at creating custom formats

[AliGW]

Administrative Note:

Sorry, but your posts do not comply with Rule #6 of our Forum RULES:

Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction, then send a private message to them asking for clarification.

All Participants:

Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.

[KarolSweats]

changing the name for better clarification

[AliGW]

Thank you.

[Croweater]

@AliGW

you say, "All Participants:

Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks."

But how do we know if the current thread title is still the one you don't like or it has been modified already?

[AliGW]

Read posts #9 and #10. The tittle has been changed and acknowledged.

[Croweater]

..but my point is, that if posts #9 and #10 hadn't been submitted, noone would know whether the title had been changed or not.

So the people who you are telling NOT to post (i.e. posts #5 and #7), would have no idea whether the title had been changed at that stage or not.

[AliGW]

Yes, they would. If the title is the same in every post in the thread, then it hasn’t been changed. When a title change is requested, the OP is asked to edit the title of the first post to the thread. This does not affect any of the existing posts below it.