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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,589
Office Version
  1. 365
Platform
  1. Windows
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?
 

elakhna

New Member
Joined
Aug 12, 2009
Messages
4
Thank you very much for your help, Mr. Peter!!! Mods, please delete this thread if possible, as it serves no future use.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,589
Office Version
  1. 365
Platform
  1. Windows
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.) :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,555
Members
417,220
Latest member
lam150498

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
Top