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?CLMMillennium Member

Messages:
7,541
2,181
Joined:
Feb 24, 1999
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

Messages:
2,188
773
Joined:
Jul 13, 2006
Location:
Virginia
=IF(A1<=2.5,A1,"Call")

3. Alcoy

Messages:
2,188
773
Joined:
Jul 13, 2006
Location:
Virginia
A1 could be all your calc in quote. Hope that helps.

4. CorkyE

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

5. DeansterCheese?CLMMillennium Member

Messages:
7,541
2,181
Joined:
Feb 24, 1999
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

Messages:
94
2
Joined:
Jan 14, 2012
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

Messages:
3,369
0
Joined:
Dec 2, 2008
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

Messages:
42,700
2,821
Joined:
Aug 16, 2001
Location:
Taunton, MA
Yuuuuuup!

Been there, done that.

9. DeansterCheese?CLMMillennium Member

Messages:
7,541
2,181
Joined:
Feb 24, 1999
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:
39
Last edited: Mar 8, 2012
10. IN RedLeg

Messages:
36
0
Joined:
Sep 22, 2010
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

Messages:
1,958
164
Joined:
Nov 17, 2001
Location:
Tempe, AZ
It's what I was going to suggest.

Messages:
7,541