close

Privacy guaranteed - Your email is not shared with anyone.

# Excel Help - if calc result >2.5, then insert text

Discussion in 'The Okie Corral' started by Deanster, Mar 8, 2012.

1. ### DeansterCheese?Millennium MemberCLM

Joined:
Feb 24, 1999
Messages:
7,214
Likes Received:
1,639
Hi all -

I should totally know how to make Excel do this, but I'm struggling. Help appreciated!

I have a fairly simple calculation in many cells of a table, which takes input values, applies a calculation, and then rounds down to half units.

=FLOOR(((F\$6-\$G\$2)/\$C\$2)+(\$B7/\$E\$2), 0.5)

and what I want it to do is to return the calculated value if it's less than or equal to 2.5, and to return the text 'Call' if it's above 2.5.

I'm getting lost in the formulation of the IF/AND statement - I know how to get it to return text strings for True/False booleans, but I can't figure out how to have it return the calculated value in one case, and a text string in another. I'm sure it's simple, but I could sure use a quick assist.

Thanks!

2. ### Alcoy

Joined:
Jul 13, 2006
Messages:
2,022
Likes Received:
232
Location:
Virginia
=IF(A1<=2.5,A1,"Call")

3. ### Alcoy

Joined:
Jul 13, 2006
Messages:
2,022
Likes Received:
232
Location:
Virginia
A1 could be all your calc in quote. Hope that helps.

4. ### CorkyE

Joined:
Jan 14, 2012
Messages:
94
Likes Received:
2
Location:
Ringgold, GA
That would be my solution... Short and simple.

5. ### DeansterCheese?Millennium MemberCLM

Joined:
Feb 24, 1999
Messages:
7,214
Likes Received:
1,639
Hmm... Looks like that's on the right track, but Since A1 (actually G8 in my case) is a formula, not a number, Excel pukes, and says I can't have the formula refer to its own cell, as it creates a circular reference...

G8=IF(FLOOR(((G\$7-\$C\$3)/\$C\$2)+(\$B8/\$C\$4), 0.5)<=2.5,G8,"Call")

or am I still doing it wrong?

Last edited: Mar 8, 2012
6. ### CorkyE

Joined:
Jan 14, 2012
Messages:
94
Likes Received:
2
Location:
Ringgold, GA
You have to put the formula in a different cell. If you don't want to see the reference cell, you can hide it.

7. ### Scared_of_zombies

Joined:
Dec 2, 2008
Messages:
3,369
Likes Received:
0
Could you copy g8 into notepad then put the row of numbers in an adjacent cell and reference that? Or are these going to change in the future?

8. ### Dennis in MAGet off my lawn

Joined:
Aug 16, 2001
Messages:
42,700
Likes Received:
2,811
Location:
Taunton, MA
Yuuuuuup!

Been there, done that.

9. ### DeansterCheese?Millennium MemberCLM

Joined:
Feb 24, 1999
Messages:
7,214
Likes Received:
1,639
right, so I can't do either of those, at least not simply.

here's what my spreadsheet looks like

Three input values - these are changed every couple weeks, and are used in the calculation for every output cell.

Input Value 1
Input Value 2
Input Value 3

Then a table with gauge values across the top,
70 80 90 100 120 140 etc

and down the side how much of a material they're putting in

10
15
20
25
etc.

Each week, we determine the three Input Values for that week, and then several times each day, someone looks at what's in front of them on the gauge, and how much material they're putting in, then uses the chart to get the calculated value of the 2nd material to be used.

So, each cell in the table has the formula ((Gauge value-Input value 2)/Input Value 1)+(Material 1 amount/Input Value 3) with the result rounded down to the nearest half unit, and referencing the Gauge value and Input Material numbers for its position in the row and column. Each formula is unique to that cell.

I just want it to do the calculation, and return the calculation as it's currently doing, unless the calculation yields a result over 2.5, in which case I want it to say 'Call', because using more than 2.5 units of the second material requires a check-in.

Attached is a screen shot showing the calculation for a particular cell, with the various references highlighted

#### Attached Files:

• ###### Screen Shot 2012-03-08 at 11.03.13 AM.png
File size:
58.8 KB
Views:
22
Last edited: Mar 8, 2012
10. ### IN RedLeg

Joined:
Sep 22, 2010
Messages:
36
Likes Received:
0
Location:
Borden, IN
=IF((FLOOR(((F\$6-\$G\$2)/\$C\$2)+(\$B7/\$E\$2), 0.5)<2.5,(FLOOR(((F\$6-\$G\$2)/\$C\$2)+(\$B7/\$E\$2), 0.5),"Call")

This should work for you.

11. ### CinicSpongy Member

Joined:
Nov 17, 2001
Messages:
1,888
Likes Received:
107
Location:
Tempe, AZ
It's what I was going to suggest.

12. ### DeansterCheese?Millennium MemberCLM

Joined:
Feb 24, 1999
Messages:
7,214
Likes Received:
1,639
Thanks! That works!

And I don't feel so bad about not being able to get that format correct now.