## Help With Excel Formulas

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Thanks so much for that explanation. Not sure I'll retain it but the good news is I did understand what you explained.
Regards,
hlewton

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Need help again and I hope my explanation is clear enough.

I am trying to create a formula and can’t get it right. I am trying to put the formula in cell K3 using the value in cell J3. However, the value in J3 can be a value of 1,2,4, or 5. Values of 4 or 5 are calculated by the formula J3-3 because the value in cell J3 should be 3 but is blank if it is OK and has a value of 3. This value can be changed by increments of 1 BUT only in a forward direction. In other words, the value could increase to 5 and then start over at 1. So, I tried to make a formula that says if J3 is less than 3, then J3 must first subtract 3 from 5 and then add whatever the number is in J3 to that value. So, if J3 has a value of 2 the steps to get to that value would require a move of 4 steps. Two examples of the formulas I tried are attached but I cannot get them to work as I need. What needs corrected to make this work? Thanks
Formula.jpg
Formula1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
hlewton

rory
5StarLounger
Posts: 794
Joined: 24 Jan 2010, 15:56

### Re: Help With Excel Formulas

Too many brackets. That would be:
=IF(J3<3,2+J3,J3-3)
Regards,
Rory

HansV
Posts: 77569
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

You don't need so many parentheses:

=IF(J3<3,5-3+J3,J3-3)

or, somewhat shorter:

=J2+IF(J3<3,2,-3)
Best wishes,
Hans

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

Thank you. I really thought I had tried the first one above but apparently I didn't since your works great.
Regards,
hlewton

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

HansV wrote:
17 Mar 2022, 11:06
You don't need so many parentheses:

=IF(J3<3,5-3+J3,J3-3)

or, somewhat shorter:

=J2+IF(J3<3,2,-3)
I used your first formula that is bold above and thought I could figure out how to make the K3 cell remain blank if nothing was in J3. I tried a few things one which was this =IF((J3<3,5-3+J3,J3-3),"") Why isn't that working for me. I looked at many of your formulas from the past that I saved just for this kind of thing and I still can't figure it out. Again, what correction do I have to make to your formula above to make the cell appear empty unless J3 has a value in it?
Regards,
hlewton

HansV
Posts: 77569
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

You have to add an extra IF to check whether J3 is blank:

=IF(J3="","",IF(J3<3,5-3+J3,J3-3))

You can also use the IFS function:

=IFS(J3="","",J3<3,5-3+J3,J3>=3,J3-3)
Best wishes,
Hans

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

HansV wrote:
17 Mar 2022, 11:54
You have to add an extra IF to check whether J3 is blank:

=IF(J3="","",IF(J3<3,5-3+J3,J3-3))

You can also use the IFS function:

=IFS(J3="","",J3<3,5-3+J3,J3>=3,J3-3)
Thank you. I thought I needed an extra IF but just didn't get it right. Strange thing is I can understand it AFTER you design it but can never seem to figure it out on my own. Thanks for your help.
Regards,
hlewton

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

I have a question about a formula that is working perfectly, with Hans’ help a while back. I do not want to change anything about the formula. However, it makes me question what I thought I knew about an Absolute Cell Reference. I’m glad it worked out the way it did but it’s not what I thought would happen. I thought I’d have to edit the Absolute Cell References, but I didn’t.

If you look at the 2 formulas pasted below you can see that the \$ signs are referencing both the row and cell location. You can also see in one formula they are “\$J\$1173:\$J\$1773” and in the other formula they are “\$J\$1302:\$J\$1902.” I needed to move the range that the formulas refer to down in the spreadsheet and even though they are Absolute Cell References, the range in the formula updated.

Admittedly I am not great at finding things on the NET and did not find an answer as to why the Absolute Cells updated themselves. My guess is that even though they are Absolute Cell References they will still update themselves if the range they are associated with moves. Is that correct?

=IFERROR(CONCATENATE(ROUND(MAX(\$J\$1173:\$J\$1773),0)," & ",ROUND(VLOOKUP(MAX(\$J\$1173:\$J\$1773),\$J\$1173:\$K\$1773,2,FALSE),0)),"")

=IFERROR(CONCATENATE(ROUND(MAX(\$J\$1302:\$J\$1902),0)," & ",ROUND(VLOOKUP(MAX(\$J\$1302:\$J\$1902),\$J\$1302:\$K\$1902,2,FALSE),0)),"")
Regards,
hlewton

HansV
Posts: 77569
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Help With Excel Formulas

An absolute reference will not change when you fill the formula down or to the right, nor when you copy the cell with the formula and paste it elsewhere.

S2086.png
S2087.png

But it will change if you move the range referred to:

S2088.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

hlewton
PlatinumLounger
Posts: 3714
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

### Re: Help With Excel Formulas

HansV wrote:
29 Dec 2022, 15:05
An absolute reference will not change when you fill the formula down or to the right, nor when you copy the cell with the formula and paste it elsewhere.

S2086.png

S2087.png

But it will change if you move the range referred to:

S2088.png
Thanks I kind of thought that was how it worked since I didn't have to manually edit the formulas.
Regards,
hlewton