Forum Discussion

mynians's avatar
mynians
Copper Contributor
Nov 16, 2023
Solved

Excel Functions Help Nested

I need the sum of 2 cells (C3+C5)
If the sum is = or less than 4 I need the cell, (E3) to read 100%
If the sum is =5 to or >9 I need the cell, (E3) to read 90%
If the sum is =>10 or <19 I need the cell, (E3) to read 80%
If the sum is =>20 or <29 I need the cell, (E3) to read 70%
If the sum is =>30 or <49 I need the cell, (E3) to read 55%
If the sum is =>50 or <99 I need the cell, (E3) to read 45%
If the sum is =>100 or <250 I need the cell, (E3) to read 35%

 

 I think this is where i needed to start 

=IF((C3+C5)>=5, "100%", "100"),
=IF((C3+C5)>5>9, "90%", "90"),
=IF((C3+C5)>10>19, "80%", "80"),
=IF((C3+C5)>20>29, "70%", "70"),
=IF((C3+C5)>30>49, "55%", "55"),
=IF((C3+C5)>50>99, "45%", "45"),
=IF((C3+C5)>99>100, "35%", "45")

 

here are what i tried without success - they do not go past 90% when i reach 10 or above the result is still 90% 

 

=IF(OR(C3+C5="<5",C3+C5=">5-<9",C3+C5=">10-<19"), 100%, 90%,80%)


=IF(OR(C3+C5="<5",C3+C5=">5-<9"), 100%, 90%)

=IF(OR(C3+C5="<5",C3+C5=">5<9",c3+c5=">10<19"), 100%, 90%, 80%)


=IF((C3+C5)>=5,"100%","100")=IF((C3+C5)>5>9,"90%","90")=IF((C3+C5)>10>19,"80%","80")=IF((C3+C5)>20>29,"70%","70")=IF((C3+C5)>30>49,"55%","55")=IF((C3+C5)>50>99,"45%","45")=IF((C3+C5)>99>100,"35%","45")


=IF(C3+C5<=4,"100%",IF(C3+C5>4-9,"90%",IF(C3+C5>10-19,"80%",IF(C3+C5>20-29,"70%",IF(C3+C5>30-49,"55%",IF(C3+C5>50-100,"45%"))))))
=IF(C3+C5<4,"100%",IF(C3+C5=5-9,"90%",IF(C3+C5=10-19,"80%",IF(C3+C5=20-29,"70%",IF(C3+C5=30-49,"55%",IF(C3+C5=50-100,"45%"))))))


=IF(C3+C5<4,100%,IF(C3+C5<OR5>9,90%,IF(C3+C5>OR10<19,80%,IF(C3+C5>OR20<30,70%,IF(C3+C5>30,55%)))))

 

=IF(C3+C5=”<4” ,100%,IF(C3+C5=”>5<9”,90%,IF(C3+C5=”>9<19”,80%,IF(C3+C5=”>20<30”,70%,IF(C3+C5=”>30<49”,55%,IF(C3+C5=”>50<100”,45%))))))

 

 

Any help would be greatly Appreciated.  thank you 

 

 

 

great this did work! thank you
The next part of this spreadsheet requires some more assistance. We have managed to obtain numbers using the formula you provided. Can we use this formula to obtain both text and numbers? We are referring to using XLOOKUP.

if (name = B6:B486) then depending on the selected text answer shows D7:D59 which ever the name matches

typed out as best i could
(input name) Column A36 pulling from Columns B6:B486 then answer should generate to column D36( a percentage number that is in the cells) from data G6 thru G486.


I don't know how to create the formula for the same procedure.

thanks in advance.

  • mynians 

    No need for the all the logical functions. Create a dedicated lookup table in your sheet with the rates then use XLOOKUP:

    =LET(total,C3+C5,IF(total>250,"no such",XLOOKUP(total,value,rate,,-1)))

     

Resources