Find Replace Constant Cell References

elakhna

New Member
Joined
Aug 12, 2009
Messages
4
Stupid/easy question: I have a long IF statement for an Excel tutorial I'm writing that aims to teach teachers how to use Excel for automated grading. It's essentially
Code:
IF(cell >= minimumgrade, lettergrade, IF(
for a LONG time. So, if the student's grade F4 = 96 and the value for an 'A' grade (J4) = 93,
Code:
=IF(F4>=J4,A,IF(F4>= ...
for a long time. However, I was wondering about constant cell references, the dollar signs around the minimumgrade cell. I was wondering if there's an easy way to apply constant cell references to a lot of cells, all of which are on the same row. So, if the grades are J4, H4, I4, etc., is there a quick way to make instance of that letter in the formula have a dollar sign before and after? It's a lot to ask 80+ year old faculty to go through and press F4 before each reference they have to make constant. I've been fiddling with Find and Replace wild cards to no avail.

The formula I made (it's really simple) is this:
Code:
=IF(F4>=J4,J3,IF(F4>=K4,K3,IF(F4>=L4,L3,IF(F4>=M4,M3,IF(F4>=N4,N3,IF(F4>=O4,O3,IF(F4>=P4,P3,IF(F4>=Q4,Q3,IF(F4>=R4,R3,IF(F4>=S4,S3,IF(F4>=T4,T3)))))))))))
The ones needing dollar signs are all cells except F4.

Thanks in advance for your help!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome to the MrExcel board! (as a poster - it appears you have been a member for quite a while :))

Not answering your question directly, but could you consider rearranging your table and use a much simpler formula similar to that suggested here?
 
Upvote 0
Thank you very much for your help, Mr. Peter!!! Mods, please delete this thread if possible, as it serves no future use.
 
Upvote 0
Thank you very much for your help, Mr. Peter!!! Mods, please delete this thread if possible, as it serves no future use.
Glad you got a resolution.

(The thread will not be deleted as it remains useful for others searching for this type of solution. Also, Moderators are volunteers and don't have the time to search out and delete all resolved threads.) :)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top