If function

%3CLINGO-SUB%20id%3D%22lingo-sub-1865132%22%20slang%3D%22en-US%22%3EIf%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865132%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20I've%20got%20the%20following%20test%2C%20that%20I%20really%20need%20help%20with.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20the%20following%20data%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%20Point%3A%2020%20Euros%3CBR%20%2F%3Eto%203%20pages%3A%200%2C5%3CBR%20%2F%3Eabove%203%20pages%3A%200%2C3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ename%20surname%20pages%20pay%3CBR%20%2F%3ENaomi%20Evans%26nbsp%3B%20%26nbsp%3B%204%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B36%3CBR%20%2F%3ETim%20Burton%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2010%3CBR%20%2F%3EIan%20Summer%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B30%3CBR%20%2F%3EMay%20Summer%26nbsp%3B%20%26nbsp%3B2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B20%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20must%20use%20If%20and%20Sum%20function%20in%20Excel%20and%20calculate%20the%20Amount%20shown%20under%20Pay%20(36%2C%2010%2C%2030%2C%2020).%3CBR%20%2F%3EThe%20amount%20of%20the%20pay%20depend%20on%20how%20many%20pages%20the%20author%20wrote%20and%20on%20the%20number%20of%20points%20he%20gets.%20If%20his%20work%20have%20from%201%20to%203%20pages%20than%20every%20page%20is%20worth%200%2C5%20point%2C%20every%20page%20above%203%20get%20only%200%2C3%20points.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sum%20would%20be%20for%20Naomi%20Evans%20the%20following%3A%3CBR%20%2F%3E20*0%2C5%3D10%3CBR%20%2F%3E20*0%2C3%3D6%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EShe%20wrote%204%20pages%20so%3A%3CBR%20%2F%3E10*3%3D30%3CBR%20%2F%3E1*6%3D6%3CBR%20%2F%3E----%3CBR%20%2F%3E36%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20how%20you%20calculate%20this%20in%20Excel%20with%20%22IF%22%20function%3F%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20I%20am%20getting%20crazy%20over%20it...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1865132%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865167%22%20slang%3D%22de-DE%22%3ESubject%3A%20If%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865167%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F861706%22%20target%3D%22_blank%22%3E%40Natalija80%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20an%20MS%20Excel%20file%20(without%20sensitive%20data)%20to%20your%20project.%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20also%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20this%20you%20help%20all%20of%20us%20here%20in%20the%20community%2C%20yourself%2C%20users%20who%20want%20to%20help%20and%20the%20other%20users%20who%20don't%20have%20to%20wait%20any%20longer%20until%20they%20get%20an%20answer.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1865219%22%20slang%3D%22en-US%22%3ERe%3A%20If%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1865219%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F861706%22%20target%3D%22_blank%22%3E%40Natalija80%3C%2FA%3E%26nbsp%3BNo%20need%20for%20a%20file.%20Your%20question%20is%20clear.%20No%20need%20for%20IF%20or%20SUM%20either.%20You%20can%20achieve%20your%20goal%20by%20using%20MIN%20and%20MAX.%20The%20attached%20fie%20demonstrates%20this%20in%2C%20though%2C%20in%20a%20simplified%20way.%20But%20perhaps%20you%20find%20it%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi. I've got the following test, that I really need help with.


I have the following data:

 

1 Point: 20 Euros
to 3 pages: 0,5
above 3 pages: 0,3

 

name surname pages pay
Naomi Evans    4         36
Tim Burton       1        10
Ian Summer     3         30
May Summer   2         20

 

I must use If and Sum function in Excel and calculate the Amount shown under Pay (36, 10, 30, 20).
The amount of the pay depend on how many pages the author wrote and on the number of points he gets. If his work have from 1 to 3 pages than every page is worth 0,5 point, every page above 3 get only 0,3 points.

 

The sum would be for Naomi Evans the following:
20*0,5=10
20*0,3=6


She wrote 4 pages so:
10*3=30
1*6=6
----
36

 

But how you calculate this in Excel with "IF" function? I am getting crazy over it...

 

Thank you

4 Replies
Highlighted

@Natalija80 

With your permission, if I can recommend you, add a MS Excel file (without sensitive data) to your project. Knowing the Excel version and operating system would also be an advantage.

With this you help all of us here in the community, yourself, users who want to help and the other users who don't have to wait any longer until they get an answer.

 

Thank you for your understanding and patience

 

 

Nikolino

 

I know I don't know anything (Socrates)

Highlighted

@Natalija80 No need for a file. Your question is clear. No need for IF or SUM either. You can achieve your goal by using MIN and MAX. The attached fie demonstrates this in, though, in a simplified way. But perhaps you find it helpful.

 

 

Highlighted
Thank you!!! But the point of the homework I was given was to use the “If” functions...Finally I found the solution =IF(D9<=3;D9*0,5;IF(D9>3;1,5+$C$6))*20. Half a day and a lot of stress but I am still really thankfull for your help. Its interesting to know how many ways there are to come to the solution ☺️
Highlighted

@Natalija80 Fair enough. Personally, I tend to avoid nested IF functions if I can. Good you figured it out yourself.