graph showing 1 decimal then 0 decimal

I have found a formula on this site "=IF($A$16=1,TEXT(IF($A$16=1,B2,B3),"0")*1,TEXT(IF($A$16=1,B2,B3),"0.0")*1)" that solved one problem for me, having a graph with two different variables in a dropdown selection, one showing the number to one decimal and the other showing the whole number.

I have another graph that has 8 options, four that should be to one decimal place OR four that would be whole numbers. I tried to modify the formula to "=IF($A$16=1,TEXT(IF($A$16=1,B2,B3,B4,B5),"0")*1,TEXT(IF($A$16=1,B2,B3,B4,B5),"0.0")*1)" but that did not work.

Any help with modifying it to cover more variables? (would prefer if I didn't have to copy a lot of data and paste it into a sheet to post if possible.)

thats doing an IF statement
TEXT(IF($A$16=1,B2,B3)

if A16 =1 , then format B2 to 0 , if a16 not equal 1 format to B3, but as A16 does not =1 , then it would not so the 2nd part of that IF any way
and move to the 0.0 part

=IF($A$16=1,TEXT(IF($A$16=1,B2,B3),"0")*1,TEXT(IF($A$16=1,B2,B3),"0.0")*1)"

I think that will only ever be

if A16 =1 - then format B2 to 0
and the 2nd part
A16 <> 1 - then format B3 to 0.0

so not really sure how that formulais helping you

other than B3 is 0.0 and B2 is 0

and so adding the extra cells - are into an if statement - so will not work

I assume - please correct

if A16 = 1
then you want
B2,B3,B4 & B5 to be text to 0 places

can you describe what you need

is it simply
if A16 =1
then B2,3,4,5 = zero places
If A16<>1
b2,3,4,5 - one place

well in the one place i'm using it, i have two options, either a count or a count per 1000 patient days. The per 1000 pt days is to normalize the data. When i want the straight count i don't want decimals, when i want the per 1000 it becomes more "granular" and i want the number to be to one decimal place.

I hope that makes sense.

so, from what you're saying, i suspect it may not be adjustable because i was working with a combo box with two selections, 1 or 2 which is how that formula was being used when i found it. Now i'm working with a combo box of essentially 8, a type of event (four of them) and shown as either a count or a count per 1000 days. So here is what i have, event types: A&B, C, D & above, All. Then those four are shown as either a count or a count per 1000 days.

I think I need to incorporate it with a vlookup stmt. Can you suggest an order for how to use it w/vlookup? Right now the formula uses a vlookup to return one of 8 values. If I can use it w/vlookup to read that if it is count (1) then no decimal places, if it is count per 1000 days (2) then return 1 decimal place.

Any chance of a sample spreadsheet - so we can see the data and where its coming from

I think I need to incorporate it with a vlookup stmt. Can you suggest an order for how to use it w/vlookup? Right now the formula uses a vlookup to return one of 8 values. If I can use it w/vlookup to read that if it is count (1) then no decimal places, if it is count per 1000 days (2) then return 1 decimal place.

Yes you can do that

so you would just change the reference

in the table you have a column with zero and a column with 1

if(A16=1, Vlookup(...... ) , vlookup(.....) )

what is the vlookup formula and the table it refers to ?

it will have a lookup reference which is the 3rd , in the vlookup(x,x,x,x)
which you can change to look up a different column

I'm working on putting it in a spreadsheet, it'll be a few more minutes.

ok, see attached spreadsheet.

I have used a conditional format to set the number of decimal places

this may not be the correct way to do it - BUT I'm basically saying
IF cell C18 =1 then format to zero decimal places

is this anywhere near a working solution - I was looking for something to use to apply to the conditional format

see attached

it isn't working for me, the counts are all 0 decimal places, even the /1000 days counts.

what version of excel

so which rows are changed

conditional format should do it
I formatted all
F2 to H6
to zero decimal

First
do all the cells F2 to H6 change to zero decimals places when count is selected

Excel 2007.

The rows F2 through H6 are all 0 decimal places regardless of whether or not I chose count or count/1000. So yes they change to zero. What I need is for them to stay at 1 when /1000 but go to whole numbers when count is selected.

so where are you selecting count
I saw that as the drop down in cell B17
and when changed to
Count or /1000
then the C18 cell changes to 1

I saw if count
if count, want formatted as in rows 9 through 12

which are all integers
and 1 decimal if not

so i'm missing the process
BUT it would appear that conditional format may help

can you describe what needs to be F2>H6 - whne count is selected in B17 drop down ?

the people using the worksheet select which they want to see: count or count / 1000 for their facility and or unit. So if they select count, that changes the selected data to come from rows 9 through 12. If /1000 then rows 13 through 16. There are two graphs that come from the data, the selection in the top dropdown box selects from cells D3 through D6. So one graph is which event types they want, A&B events or C or etc. What goes into a different graph is the data in cells F3 through H16 and whether or not it is count or count /1000.

So the base data for the graphs comes from cells F9 through H12 (count) or F13 through H16 (/1000). The way they are formatted in those cells is the way I'd like them to be formatted in my graph. Because I don't want 7.3 and 6.6 to be rounded to 7 (cells F16 and G16) I have it formatted to 1 decimal. But then the graph shows the numbers in F9 through H12 also as 37.0 and 39.0 and 51.0 (F9 - H9). That is why I've been looking for a formula to allow me to, in one case w/one set of numbers show it whole and in another case show it by the decimal.

Count comes from D18. If you right click on the dropdown box in B17 and click on format control you can see which cells it is linked to (input range D19:D20 and cell link C18).

In F2-H6 needs to be the numbers in cells F9-H16, either count (F9-H12) or count/1000 (F13-H16) and formatted as they are in those cells.

Hopefully that is clear. Thanks for pursuing this.

i thought thats what I had done

the format in H2:H6 now matches the table in F9:H17

In F2-H6 needs to be the numbers in cells F9-H16, either count (F9-H12) or count/1000 (F13-H16) and formatted as they are in those cells.

that what it looks like in my sheet

see images attached

Ok, it works, it is weird in that for me to get it to work I have to double click in another cell then it'll change from 7 to 7.3. I'll try it on a larger scale.

Thank you VERY MUCH for the help.

I have to tell you, even though it solved the issue of how the data shows, the graph is still showing both metrics at 1 decimal. Check the two images.

i have not been able to resolve that issue - hopefully someone else can help