Vake

Okay, so this is what I'm trying to do.

I'm tryin to have put in multiple IF functions into one cell. It will check the value of another cell, and according to its rounded value (to the nearest .05) , it will give a preditermined value.

Ex.

IF(ROUND B4 1.05, F4=53)
IF(ROUND B4 1.1, F4=51)
IF(ROUND B4 1.15, F4=49)

and so on...

How can I put this into on cell? I've got 15 of these IF statements to put in in total.

-NPC

gistek

You can write your individual equations one in each line WITHOUT the equals sign.

Then combine them using the Concatenate formula.

Next copy the contents of this cell to Notepad.

Then copy the formula from notepad to the cell content bar. Do this by selecting the target cell, clicking the box next to the "fx" below the toolbar, and using the paste command.

Based on your example, the formula development spreadsheet would look like the following:

IF(ROUND(B4,2)=1.05,53,
IF(ROUND(B4,1)=1.1,51,
IF(ROUND(B4,2)=1.15,49)))

=CONCATENATE("=",F5,F6,F7)

Note, the CONCATENATE function must have the equal sign in quotes and you can't use the F5:F7 shortcut that works for the SUM function.

You will also need to add one ")" at the end of the formula for each nested IF. This can be done by including them with the last formula or by including them in the CONCATENATE function. =CONCATENATE("=",F5,F6,F7,"))))")

Vake

Thank you for the help mate.

Works like a charm now

macropod

Hi Vake,

You have a problem with your logic, in that both ROUND(B4,2)=1.05 and ROUND(B4,1)=1.1 can be true at the same time.

Assuming you really want to base your output on the input rounded to the nearest 0.05 and that your numers only fall in the range >=1.025 to <1.175, you could use a formula in F4 coded as:
=51+(ROUND(B4*2,1)/2=1.05)*2-(ROUND(B4*2,1)/2=1.15)*2
Note the absence of IF tests! I'll leave it to you to decipher the formula's workings.

gistek

Good catch, macropod. I haven't tried your formula, but your post did remind me that in order to ROUND by skip-counts (2's, 5's etc) you may have to modify the number, round the result, then reverse the modification.

I'm not up to working the specifics out today. (neck pain due to the pinched cord)

Vake

Thank you for the added thought mate, and I see what you mean.


I'm not sure how to increment that into my table though, and make it work as a variable for a different value each time.

gistek

Basically it breaks down as follows:
A1 contains 1.06
A2 contains = A3*2 = 2.12
A3 contains = ROUND(A2*2,1) = 2.1
A4 contains = A3/2 = 1.05

Here’s the table I have so far:
CountBy, Use, RoundTo
0.05, 2, 1
0.5, 2, 0
5, 2, -1
0.02, 5, 1
0.2, 5, 0
2, 5, -1

I’m having some problems counting by other numbers, though.

macropod

Hi Vake,

Since you've got 15 number ranges to work with, you can probably use a formula in F4 coded as:
=X+ROUND((Y-B4)*2,1)*20
where X is the output number for the 8th item in the series and Y is the corresponding input number for an exact match. For example:
=45+ROUND((1.25-B4)*2,1)*20
With the right I/O values for the 8th item in the series, you'd get the correct output for any of the 15 possible ranges.