Help With Excel Formulas

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Help With Excel Formulas

Post by rory »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

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

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

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

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Help With Excel Formulas

Post by HansV »

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

User avatar
hlewton
PlatinumLounger
Posts: 3787
Joined: 24 Oct 2010, 23:39
Location: Canton, Ohio USA

Re: Help With Excel Formulas

Post by hlewton »

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