return odd number in 1000

L

LarsSJensen

New Member
Joined
Feb 16, 2018
Messages
10
Hi

I have alot of data, that i need to convert.


[TABLE="width: 500"]
<tbody>[TR]
[TD]value in system[/TD]
[TD]new value (the formula that i need)[/TD]
[/TR]
[TR]
[TD]8726,36[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]8971,84[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]56724,62[/TD]
[TD]57000[/TD]
[/TR]
[TR]
[TD]7000,1[/TD]
[TD]9000[/TD]
[/TR]
[TR]
[TD]846618,09[/TD]
[TD]847000[/TD]
[/TR]
</tbody>[/TABLE]

what i got so fare is =ROUNDUP(ODD((ROUNDUP(R3;-3)));-3)

but it doesnt work

i need the value in the system to roundup by the thousand, and if that value is odd (7000 is odd, and 8000 is evan) then nothing, but if the value is even, then raound up to the to an odd thousand value

Hope it makes sence, and hope you guys can help me
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Just to understand a little more.. because the 1000 multiples are by definition even, I can see why this isn't working for you...

I presume that what you mean is if the numbers preceding the zeros is odd (i.e. 847) then use it, whereas if the number is 848 (000) then it needs to round up to the next 1000..
 
Upvote 0
Hi Lars,

How about:
=ODD(ROUNDUP(A1/1000,0))*1000
 
Upvote 0
VBA Geek said:
Excel 2010
AB
value in systemnew value (the formula that i need)

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8726.36[/TD]
[TD="align: right"]9000[/TD]

</tbody>
Sheet23​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=ISEVEN(CEILING(A2,1000)/1000)*1000+CEILING(A2,1000)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
wow really quick answer, yeah the formular you posted works for me thanks
 
Upvote 0
Hi

i tested your formula as well, and it works (from my small test) but what is the difference between your and =ISEVEN(CEILING(A2,1000)/1000)*1000+CEILING(A2,1000) ??
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"><select class="tw-lang-selector vk_txt" data-dslc="da" data-dsln="Dansk" data-uiln="dansk" data-url="/setprefs?sig=0_5Mr30-pg4itgwQfPYWy-PjgqqrU%3D&prev=https://www.google.dk/search?q%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26rlz%3D1C1GCEA_enDK781DK781%26oq%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26aqs%3Dchrome..69i57j0l5.3863j0j7%26sourceid%3Dchrome%26ie%3DUTF-8" id="tw-sl" data-dlm="Registrer sprog" jsaction="change:tob.lc" data-ved="0ahUKEwj1oNuZtKrZAhWLqaQKHfo5BD0Q8TQIKjAA" style="background: transparent; border-width: initial; border-style: none; border-color: initial; color: rgb(84, 84, 84); list-style: none; margin: 0px; outline: none; height: 20px; -webkit-appearance: none; width: 79px; font-size: 13px !important;"><option value="---" disabled="">---------------</option><option value="auto" id="tw-dslo">Registrer sprog</option><option value="af">Afrikaans</option><option value="sq">Albansk</option><option value="am">Amharisk</option><option value="ar">Arabisk</option><option value="hy">Armensk</option><option value="az">Aserbajdsjansk</option><option value="eu">Baskisk</option><option value="bn">Bengali</option><option value="bs">Bosnisk</option><option value="bg">Bulgarsk</option><option value="my">Burmesisk</option><option value="ca">Catalansk</option><option value="ceb">Cebuano</option><option selected="1" value="da">Dansk</option><option value="en">Engelsk</option><option value="eo">Esperanto</option><option value="et">Estisk</option><option value="fi">Finsk</option><option value="fr">Fransk</option><option value="fy">Frisisk</option><option value="gl">Galicisk</option><option value="ka">Georgisk</option><option value="el">Græsk</option><option value="gu">Gujarati</option><option value="ht">Haitisk</option><option value="ha">Hausa</option><option value="haw">Hawaiiansk</option><option value="iw">Hebraisk</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="nl">Hollandsk</option><option value="be">Hviderussisk</option><option value="ig">Igbo</option><option value="id">Indonesisk</option><option value="ga">Irsk</option><option value="xh">IsiXhosa</option><option value="is">Islandsk</option><option value="it">Italiensk</option><option value="ja">Japansk</option><option value="jv">Javanesisk</option><option value="yi">Jiddisch</option><option value="kn">Kannada</option><option value="kk">Kasakhisk</option><option value="km">Khmer</option><option value="zh-CN">Kinesisk (forenklet)</option><option value="zh-TW">Kinesisk (traditionelt)</option><option value="ky">Kirgisisk</option><option value="ko">Koreansk</option><option value="co">Korsikansk</option><option value="hr">Kroatisk</option><option value="ku">Kurdisk</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Lettisk</option><option value="lt">Litauisk</option><option value="lb">Luxembourgsk</option><option value="mk">Makedonsk</option><option value="mg">Malagassisk</option><option value="ms">Malajisk</option><option value="ml">Malayalam</option><option value="mt">Maltesisk</option><option value="mi">Maori</option><option value="mr">Marathisk</option><option value="mn">Mongolsk</option><option value="ne">Nepalesisk</option><option value="no">Norsk</option><option value="ny">Nyanja</option><option value="ps">Pashto</option><option value="fa">Persisk</option><option value="pl">Polsk</option><option value="pt">Portugisisk</option><option value="pa">Punjabisk</option><option value="ro">Rumænsk</option><option value="ru">Russisk</option><option value="sm">Samoansk</option><option value="sr">Serbisk</option><option value="sn">Shona</option><option value="sd">Sindhi</option><option value="si">Singalesisk</option><option value="gd">Skotsk gælisk</option><option value="sk">Slovakisk</option><option value="sl">Slovensk</option><option value="so">Somali</option><option value="es">Spansk</option><option value="su">Sundanesisk</option><option value="sv">Svensk</option><option value="sw">Swahili</option><option value="st">Sydsotho</option><option value="tg">Tadsjikisk</option><option value="tl">Tagalog</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="cs">Tjekkisk</option><option value="tr">Tyrkisk</option><option value="de">Tysk</option><option value="uk">Ukrainsk</option><option value="hu">Ungarsk</option><option value="ur">Urdu</option><option value="uz">Usbekisk</option><option value="vi">Vietnamesisk</option><option value="cy">Walisisk</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select><select class="tw-lang-selector vk_txt vk_dgy tw-dl" style="color: rgb(84, 84, 84); background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-width: initial; border-style: none; border-color: initial; list-style: none; margin: 0px; outline: none; height: 20px; position: absolute; visibility: hidden; font-size: 13px !important;"><option>Dansk</option></select>
[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR]



the difference


<select class="tw-lang-selector vk_txt" data-dslc="da" data-dsln="Dansk" data-uiln="dansk" data-url="/setprefs?sig=0_5Mr30-pg4itgwQfPYWy-PjgqqrU%3D&prev=https://www.google.dk/search?q%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26rlz%3D1C1GCEA_enDK781DK781%26oq%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26aqs%3Dchrome..69i57j0l5.3863j0j7%26sourceid%3Dchrome%26ie%3DUTF-8" id="tw-sl" data-dlm="Registrer sprog" jsaction="change:tob.lc" data-ved="0ahUKEwj1oNuZtKrZAhWLqaQKHfo5BD0Q8TQIKjAA" style="background: transparent; border-width: initial; border-style: none; border-color: initial; color: rgb(84, 84, 84); list-style: none; margin: 0px; outline: none; height: 20px; -webkit-appearance: none; width: 79px; font-size: 13px !important;"><option value="---" disabled="">---------------</option><option value="auto" id="tw-dslo">Registrer sprog</option><option value="af">Afrikaans</option><option value="sq">Albansk</option><option value="am">Amharisk</option><option value="ar">Arabisk</option><option value="hy">Armensk</option><option value="az">Aserbajdsjansk</option><option value="eu">Baskisk</option><option value="bn">Bengali</option><option value="bs">Bosnisk</option><option value="bg">Bulgarsk</option><option value="my">Burmesisk</option><option value="ca">Catalansk</option><option value="ceb">Cebuano</option><option selected="1" value="da">Dansk</option><option value="en">Engelsk</option><option value="eo">Esperanto</option><option value="et">Estisk</option><option value="fi">Finsk</option><option value="fr">Fransk</option><option value="fy">Frisisk</option><option value="gl">Galicisk</option><option value="ka">Georgisk</option><option value="el">Græsk</option><option value="gu">Gujarati</option><option value="ht">Haitisk</option><option value="ha">Hausa</option><option value="haw">Hawaiiansk</option><option value="iw">Hebraisk</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="nl">Hollandsk</option><option value="be">Hviderussisk</option><option value="ig">Igbo</option><option value="id">Indonesisk</option><option value="ga">Irsk</option><option value="xh">IsiXhosa</option><option value="is">Islandsk</option><option value="it">Italiensk</option><option value="ja">Japansk</option><option value="jv">Javanesisk</option><option value="yi">Jiddisch</option><option value="kn">Kannada</option><option value="kk">Kasakhisk</option><option value="km">Khmer</option><option value="zh-CN">Kinesisk (forenklet)</option><option value="zh-TW">Kinesisk (traditionelt)</option><option value="ky">Kirgisisk</option><option value="ko">Koreansk</option><option value="co">Korsikansk</option><option value="hr">Kroatisk</option><option value="ku">Kurdisk</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Lettisk</option><option value="lt">Litauisk</option><option value="lb">Luxembourgsk</option><option value="mk">Makedonsk</option><option value="mg">Malagassisk</option><option value="ms">Malajisk</option><option value="ml">Malayalam</option><option value="mt">Maltesisk</option><option value="mi">Maori</option><option value="mr">Marathisk</option><option value="mn">Mongolsk</option><option value="ne">Nepalesisk</option><option value="no">Norsk</option><option value="ny">Nyanja</option><option value="ps">Pashto</option><option value="fa">Persisk</option><option value="pl">Polsk</option><option value="pt">Portugisisk</option><option value="pa">Punjabisk</option><option value="ro">Rumænsk</option><option value="ru">Russisk</option><option value="sm">Samoansk</option><option value="sr">Serbisk</option><option value="sn">Shona</option><option value="sd">Sindhi</option><option value="si">Singalesisk</option><option value="gd">Skotsk gælisk</option><option value="sk">Slovakisk</option><option value="sl">Slovensk</option><option value="so">Somali</option><option value="es">Spansk</option><option value="su">Sundanesisk</option><option value="sv">Svensk</option><option value="sw">Swahili</option><option value="st">Sydsotho</option><option value="tg">Tadsjikisk</option><option value="tl">Tagalog</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="cs">Tjekkisk</option><option value="tr">Tyrkisk</option><option value="de">Tysk</option><option value="uk">Ukrainsk</option><option value="hu">Ungarsk</option><option value="ur">Urdu</option><option value="uz">Usbekisk</option><option value="vi">Vietnamesisk</option><option value="cy">Walisisk</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select><select class="tw-lang-selector vk_txt vk_dgy tw-dl" style="color: rgb(84, 84, 84); background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-width: initial; border-style: none; border-color: initial; list-style: none; margin: 0px; outline: none; height: 20px; position: absolute; visibility: hidden; font-size: 13px !important;"><option>Dansk</option></select>
[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR][COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR]



the difference


<select class="tw-lang-selector vk_txt" data-dslc="da" data-dsln="Dansk" data-uiln="dansk" data-url="/setprefs?sig=0_5Mr30-pg4itgwQfPYWy-PjgqqrU%3D&prev=https://www.google.dk/search?q%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26rlz%3D1C1GCEA_enDK781DK781%26oq%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26aqs%3Dchrome..69i57j0l5.3863j0j7%26sourceid%3Dchrome%26ie%3DUTF-8" id="tw-sl" data-dlm="Registrer sprog" jsaction="change:tob.lc" data-ved="0ahUKEwj1oNuZtKrZAhWLqaQKHfo5BD0Q8TQIKjAA" style="background: transparent; border-width: initial; border-style: none; border-color: initial; color: rgb(84, 84, 84); list-style: none; margin: 0px; outline: none; height: 20px; -webkit-appearance: none; width: 79px; font-size: 13px !important;"><option value="---" disabled="">---------------</option><option value="auto" id="tw-dslo">Registrer sprog</option><option value="af">Afrikaans</option><option value="sq">Albansk</option><option value="am">Amharisk</option><option value="ar">Arabisk</option><option value="hy">Armensk</option><option value="az">Aserbajdsjansk</option><option value="eu">Baskisk</option><option value="bn">Bengali</option><option value="bs">Bosnisk</option><option value="bg">Bulgarsk</option><option value="my">Burmesisk</option><option value="ca">Catalansk</option><option value="ceb">Cebuano</option><option selected="1" value="da">Dansk</option><option value="en">Engelsk</option><option value="eo">Esperanto</option><option value="et">Estisk</option><option value="fi">Finsk</option><option value="fr">Fransk</option><option value="fy">Frisisk</option><option value="gl">Galicisk</option><option value="ka">Georgisk</option><option value="el">Græsk</option><option value="gu">Gujarati</option><option value="ht">Haitisk</option><option value="ha">Hausa</option><option value="haw">Hawaiiansk</option><option value="iw">Hebraisk</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="nl">Hollandsk</option><option value="be">Hviderussisk</option><option value="ig">Igbo</option><option value="id">Indonesisk</option><option value="ga">Irsk</option><option value="xh">IsiXhosa</option><option value="is">Islandsk</option><option value="it">Italiensk</option><option value="ja">Japansk</option><option value="jv">Javanesisk</option><option value="yi">Jiddisch</option><option value="kn">Kannada</option><option value="kk">Kasakhisk</option><option value="km">Khmer</option><option value="zh-CN">Kinesisk (forenklet)</option><option value="zh-TW">Kinesisk (traditionelt)</option><option value="ky">Kirgisisk</option><option value="ko">Koreansk</option><option value="co">Korsikansk</option><option value="hr">Kroatisk</option><option value="ku">Kurdisk</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Lettisk</option><option value="lt">Litauisk</option><option value="lb">Luxembourgsk</option><option value="mk">Makedonsk</option><option value="mg">Malagassisk</option><option value="ms">Malajisk</option><option value="ml">Malayalam</option><option value="mt">Maltesisk</option><option value="mi">Maori</option><option value="mr">Marathisk</option><option value="mn">Mongolsk</option><option value="ne">Nepalesisk</option><option value="no">Norsk</option><option value="ny">Nyanja</option><option value="ps">Pashto</option><option value="fa">Persisk</option><option value="pl">Polsk</option><option value="pt">Portugisisk</option><option value="pa">Punjabisk</option><option value="ro">Rumænsk</option><option value="ru">Russisk</option><option value="sm">Samoansk</option><option value="sr">Serbisk</option><option value="sn">Shona</option><option value="sd">Sindhi</option><option value="si">Singalesisk</option><option value="gd">Skotsk gælisk</option><option value="sk">Slovakisk</option><option value="sl">Slovensk</option><option value="so">Somali</option><option value="es">Spansk</option><option value="su">Sundanesisk</option><option value="sv">Svensk</option><option value="sw">Swahili</option><option value="st">Sydsotho</option><option value="tg">Tadsjikisk</option><option value="tl">Tagalog</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="cs">Tjekkisk</option><option value="tr">Tyrkisk</option><option value="de">Tysk</option><option value="uk">Ukrainsk</option><option value="hu">Ungarsk</option><option value="ur">Urdu</option><option value="uz">Usbekisk</option><option value="vi">Vietnamesisk</option><option value="cy">Walisisk</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select><select class="tw-lang-selector vk_txt vk_dgy tw-dl" style="color: rgb(84, 84, 84); background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-width: initial; border-style: none; border-color: initial; list-style: none; margin: 0px; outline: none; height: 20px; position: absolute; visibility: hidden; font-size: 13px !important;"><option>Dansk</option></select>
[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR][COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR]



the difference


<select class="tw-lang-selector vk_txt" data-dslc="da" data-dsln="Dansk" data-uiln="dansk" data-url="/setprefs?sig=0_5Mr30-pg4itgwQfPYWy-PjgqqrU%3D&prev=https://www.google.dk/search?q%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26rlz%3D1C1GCEA_enDK781DK781%26oq%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26aqs%3Dchrome..69i57j0l5.3863j0j7%26sourceid%3Dchrome%26ie%3DUTF-8" id="tw-sl" data-dlm="Registrer sprog" jsaction="change:tob.lc" data-ved="0ahUKEwj1oNuZtKrZAhWLqaQKHfo5BD0Q8TQIKjAA" style="background: transparent; border-width: initial; border-style: none; border-color: initial; color: rgb(84, 84, 84); list-style: none; margin: 0px; outline: none; height: 20px; -webkit-appearance: none; width: 79px; font-size: 13px !important;"><option value="---" disabled="">---------------</option><option value="auto" id="tw-dslo">Registrer sprog</option><option value="af">Afrikaans</option><option value="sq">Albansk</option><option value="am">Amharisk</option><option value="ar">Arabisk</option><option value="hy">Armensk</option><option value="az">Aserbajdsjansk</option><option value="eu">Baskisk</option><option value="bn">Bengali</option><option value="bs">Bosnisk</option><option value="bg">Bulgarsk</option><option value="my">Burmesisk</option><option value="ca">Catalansk</option><option value="ceb">Cebuano</option><option selected="1" value="da">Dansk</option><option value="en">Engelsk</option><option value="eo">Esperanto</option><option value="et">Estisk</option><option value="fi">Finsk</option><option value="fr">Fransk</option><option value="fy">Frisisk</option><option value="gl">Galicisk</option><option value="ka">Georgisk</option><option value="el">Græsk</option><option value="gu">Gujarati</option><option value="ht">Haitisk</option><option value="ha">Hausa</option><option value="haw">Hawaiiansk</option><option value="iw">Hebraisk</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="nl">Hollandsk</option><option value="be">Hviderussisk</option><option value="ig">Igbo</option><option value="id">Indonesisk</option><option value="ga">Irsk</option><option value="xh">IsiXhosa</option><option value="is">Islandsk</option><option value="it">Italiensk</option><option value="ja">Japansk</option><option value="jv">Javanesisk</option><option value="yi">Jiddisch</option><option value="kn">Kannada</option><option value="kk">Kasakhisk</option><option value="km">Khmer</option><option value="zh-CN">Kinesisk (forenklet)</option><option value="zh-TW">Kinesisk (traditionelt)</option><option value="ky">Kirgisisk</option><option value="ko">Koreansk</option><option value="co">Korsikansk</option><option value="hr">Kroatisk</option><option value="ku">Kurdisk</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Lettisk</option><option value="lt">Litauisk</option><option value="lb">Luxembourgsk</option><option value="mk">Makedonsk</option><option value="mg">Malagassisk</option><option value="ms">Malajisk</option><option value="ml">Malayalam</option><option value="mt">Maltesisk</option><option value="mi">Maori</option><option value="mr">Marathisk</option><option value="mn">Mongolsk</option><option value="ne">Nepalesisk</option><option value="no">Norsk</option><option value="ny">Nyanja</option><option value="ps">Pashto</option><option value="fa">Persisk</option><option value="pl">Polsk</option><option value="pt">Portugisisk</option><option value="pa">Punjabisk</option><option value="ro">Rumænsk</option><option value="ru">Russisk</option><option value="sm">Samoansk</option><option value="sr">Serbisk</option><option value="sn">Shona</option><option value="sd">Sindhi</option><option value="si">Singalesisk</option><option value="gd">Skotsk gælisk</option><option value="sk">Slovakisk</option><option value="sl">Slovensk</option><option value="so">Somali</option><option value="es">Spansk</option><option value="su">Sundanesisk</option><option value="sv">Svensk</option><option value="sw">Swahili</option><option value="st">Sydsotho</option><option value="tg">Tadsjikisk</option><option value="tl">Tagalog</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="cs">Tjekkisk</option><option value="tr">Tyrkisk</option><option value="de">Tysk</option><option value="uk">Ukrainsk</option><option value="hu">Ungarsk</option><option value="ur">Urdu</option><option value="uz">Usbekisk</option><option value="vi">Vietnamesisk</option><option value="cy">Walisisk</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select><select class="tw-lang-selector vk_txt vk_dgy tw-dl" style="color: rgb(84, 84, 84); background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-width: initial; border-style: none; border-color: initial; list-style: none; margin: 0px; outline: none; height: 20px; position: absolute; visibility: hidden; font-size: 13px !important;"><option>Dansk</option></select>
[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR][COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR]



the difference


<select class="tw-lang-selector vk_txt" data-dslc="da" data-dsln="Dansk" data-uiln="dansk" data-url="/setprefs?sig=0_5Mr30-pg4itgwQfPYWy-PjgqqrU%3D&prev=https://www.google.dk/search?q%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26rlz%3D1C1GCEA_enDK781DK781%26oq%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26aqs%3Dchrome..69i57j0l5.3863j0j7%26sourceid%3Dchrome%26ie%3DUTF-8" id="tw-sl" data-dlm="Registrer sprog" jsaction="change:tob.lc" data-ved="0ahUKEwj1oNuZtKrZAhWLqaQKHfo5BD0Q8TQIKjAA" style="background: transparent; border-width: initial; border-style: none; border-color: initial; color: rgb(84, 84, 84); list-style: none; margin: 0px; outline: none; height: 20px; -webkit-appearance: none; width: 79px; font-size: 13px !important;"><option value="---" disabled="">---------------</option><option value="auto" id="tw-dslo">Registrer sprog</option><option value="af">Afrikaans</option><option value="sq">Albansk</option><option value="am">Amharisk</option><option value="ar">Arabisk</option><option value="hy">Armensk</option><option value="az">Aserbajdsjansk</option><option value="eu">Baskisk</option><option value="bn">Bengali</option><option value="bs">Bosnisk</option><option value="bg">Bulgarsk</option><option value="my">Burmesisk</option><option value="ca">Catalansk</option><option value="ceb">Cebuano</option><option selected="1" value="da">Dansk</option><option value="en">Engelsk</option><option value="eo">Esperanto</option><option value="et">Estisk</option><option value="fi">Finsk</option><option value="fr">Fransk</option><option value="fy">Frisisk</option><option value="gl">Galicisk</option><option value="ka">Georgisk</option><option value="el">Græsk</option><option value="gu">Gujarati</option><option value="ht">Haitisk</option><option value="ha">Hausa</option><option value="haw">Hawaiiansk</option><option value="iw">Hebraisk</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="nl">Hollandsk</option><option value="be">Hviderussisk</option><option value="ig">Igbo</option><option value="id">Indonesisk</option><option value="ga">Irsk</option><option value="xh">IsiXhosa</option><option value="is">Islandsk</option><option value="it">Italiensk</option><option value="ja">Japansk</option><option value="jv">Javanesisk</option><option value="yi">Jiddisch</option><option value="kn">Kannada</option><option value="kk">Kasakhisk</option><option value="km">Khmer</option><option value="zh-CN">Kinesisk (forenklet)</option><option value="zh-TW">Kinesisk (traditionelt)</option><option value="ky">Kirgisisk</option><option value="ko">Koreansk</option><option value="co">Korsikansk</option><option value="hr">Kroatisk</option><option value="ku">Kurdisk</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Lettisk</option><option value="lt">Litauisk</option><option value="lb">Luxembourgsk</option><option value="mk">Makedonsk</option><option value="mg">Malagassisk</option><option value="ms">Malajisk</option><option value="ml">Malayalam</option><option value="mt">Maltesisk</option><option value="mi">Maori</option><option value="mr">Marathisk</option><option value="mn">Mongolsk</option><option value="ne">Nepalesisk</option><option value="no">Norsk</option><option value="ny">Nyanja</option><option value="ps">Pashto</option><option value="fa">Persisk</option><option value="pl">Polsk</option><option value="pt">Portugisisk</option><option value="pa">Punjabisk</option><option value="ro">Rumænsk</option><option value="ru">Russisk</option><option value="sm">Samoansk</option><option value="sr">Serbisk</option><option value="sn">Shona</option><option value="sd">Sindhi</option><option value="si">Singalesisk</option><option value="gd">Skotsk gælisk</option><option value="sk">Slovakisk</option><option value="sl">Slovensk</option><option value="so">Somali</option><option value="es">Spansk</option><option value="su">Sundanesisk</option><option value="sv">Svensk</option><option value="sw">Swahili</option><option value="st">Sydsotho</option><option value="tg">Tadsjikisk</option><option value="tl">Tagalog</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="cs">Tjekkisk</option><option value="tr">Tyrkisk</option><option value="de">Tysk</option><option value="uk">Ukrainsk</option><option value="hu">Ungarsk</option><option value="ur">Urdu</option><option value="uz">Usbekisk</option><option value="vi">Vietnamesisk</option><option value="cy">Walisisk</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select><select class="tw-lang-selector vk_txt vk_dgy tw-dl" style="color: rgb(84, 84, 84); background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-width: initial; border-style: none; border-color: initial; list-style: none; margin: 0px; outline: none; height: 20px; position: absolute; visibility: hidden; font-size: 13px !important;"><option>Dansk</option></select>
[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR][COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR]



the difference


<select class="tw-lang-selector vk_txt" data-dslc="da" data-dsln="Dansk" data-uiln="dansk" data-url="/setprefs?sig=0_5Mr30-pg4itgwQfPYWy-PjgqqrU%3D&prev=https://www.google.dk/search?q%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26rlz%3D1C1GCEA_enDK781DK781%26oq%3Dforskellen%2Bp%25C3%25A5%2Bengelsk%26aqs%3Dchrome..69i57j0l5.3863j0j7%26sourceid%3Dchrome%26ie%3DUTF-8" id="tw-sl" data-dlm="Registrer sprog" jsaction="change:tob.lc" data-ved="0ahUKEwj1oNuZtKrZAhWLqaQKHfo5BD0Q8TQIKjAA" style="background: transparent; border-width: initial; border-style: none; border-color: initial; color: rgb(84, 84, 84); list-style: none; margin: 0px; outline: none; height: 20px; -webkit-appearance: none; width: 79px; font-size: 13px !important;"><option value="---" disabled="">---------------</option><option value="auto" id="tw-dslo">Registrer sprog</option><option value="af">Afrikaans</option><option value="sq">Albansk</option><option value="am">Amharisk</option><option value="ar">Arabisk</option><option value="hy">Armensk</option><option value="az">Aserbajdsjansk</option><option value="eu">Baskisk</option><option value="bn">Bengali</option><option value="bs">Bosnisk</option><option value="bg">Bulgarsk</option><option value="my">Burmesisk</option><option value="ca">Catalansk</option><option value="ceb">Cebuano</option><option selected="1" value="da">Dansk</option><option value="en">Engelsk</option><option value="eo">Esperanto</option><option value="et">Estisk</option><option value="fi">Finsk</option><option value="fr">Fransk</option><option value="fy">Frisisk</option><option value="gl">Galicisk</option><option value="ka">Georgisk</option><option value="el">Græsk</option><option value="gu">Gujarati</option><option value="ht">Haitisk</option><option value="ha">Hausa</option><option value="haw">Hawaiiansk</option><option value="iw">Hebraisk</option><option value="hi">Hindi</option><option value="hmn">Hmong</option><option value="nl">Hollandsk</option><option value="be">Hviderussisk</option><option value="ig">Igbo</option><option value="id">Indonesisk</option><option value="ga">Irsk</option><option value="xh">IsiXhosa</option><option value="is">Islandsk</option><option value="it">Italiensk</option><option value="ja">Japansk</option><option value="jv">Javanesisk</option><option value="yi">Jiddisch</option><option value="kn">Kannada</option><option value="kk">Kasakhisk</option><option value="km">Khmer</option><option value="zh-CN">Kinesisk (forenklet)</option><option value="zh-TW">Kinesisk (traditionelt)</option><option value="ky">Kirgisisk</option><option value="ko">Koreansk</option><option value="co">Korsikansk</option><option value="hr">Kroatisk</option><option value="ku">Kurdisk</option><option value="lo">Lao</option><option value="la">Latin</option><option value="lv">Lettisk</option><option value="lt">Litauisk</option><option value="lb">Luxembourgsk</option><option value="mk">Makedonsk</option><option value="mg">Malagassisk</option><option value="ms">Malajisk</option><option value="ml">Malayalam</option><option value="mt">Maltesisk</option><option value="mi">Maori</option><option value="mr">Marathisk</option><option value="mn">Mongolsk</option><option value="ne">Nepalesisk</option><option value="no">Norsk</option><option value="ny">Nyanja</option><option value="ps">Pashto</option><option value="fa">Persisk</option><option value="pl">Polsk</option><option value="pt">Portugisisk</option><option value="pa">Punjabisk</option><option value="ro">Rumænsk</option><option value="ru">Russisk</option><option value="sm">Samoansk</option><option value="sr">Serbisk</option><option value="sn">Shona</option><option value="sd">Sindhi</option><option value="si">Singalesisk</option><option value="gd">Skotsk gælisk</option><option value="sk">Slovakisk</option><option value="sl">Slovensk</option><option value="so">Somali</option><option value="es">Spansk</option><option value="su">Sundanesisk</option><option value="sv">Svensk</option><option value="sw">Swahili</option><option value="st">Sydsotho</option><option value="tg">Tadsjikisk</option><option value="tl">Tagalog</option><option value="ta">Tamil</option><option value="te">Telugu</option><option value="th">Thai</option><option value="cs">Tjekkisk</option><option value="tr">Tyrkisk</option><option value="de">Tysk</option><option value="uk">Ukrainsk</option><option value="hu">Ungarsk</option><option value="ur">Urdu</option><option value="uz">Usbekisk</option><option value="vi">Vietnamesisk</option><option value="cy">Walisisk</option><option value="yo">Yoruba</option><option value="zu">Zulu</option></select><select class="tw-lang-selector vk_txt vk_dgy tw-dl" style="color: rgb(84, 84, 84); background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; border-width: initial; border-style: none; border-color: initial; list-style: none; margin: 0px; outline: none; height: 20px; position: absolute; visibility: hidden; font-size: 13px !important;"><option>Dansk</option></select>
[COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR][COLOR=rgba(0, 0, 0, 0.54)]<svg focusable="false" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24">
</path></svg>[/COLOR]
[/COLOR]


the difference


</body>
 
Upvote 0
ohh i tested both in my data set, and if the system value is e.g -59.5


[TABLE="width: 500"]
<tbody>[TR]
[TD]system value[/TD]
[TD]=ISEVEN(CEILING(A2,1000)/1000)*1000+CEILING(A2,1000)[/TD]
[TD]=ODD(ROUNDUP(A1/1000,0))*1000[/TD]
[/TR]
[TR]
[TD]-59,5[/TD]
[TD]1000[/TD]
[TD]-1000[/TD]
[/TR]
</tbody>[/TABLE]

and here i want the value 1000

its regarding calculation customers credit limit for work
 
Upvote 0
LarsSJensen said:
ohh i tested both in my data set, and if the system value is e.g -59.5


[TABLE="width: 500"]
<tbody>[TR]
[TD]system value[/TD]
[TD]=ISEVEN(CEILING(A2,1000)/1000)*1000+CEILING(A2,1000)[/TD]
[TD]=ODD(ROUNDUP(A1/1000,0))*1000[/TD]
[/TR]
[TR]
[TD]-59,5[/TD]
[TD]1000[/TD]
[TD]-1000[/TD]
[/TR]
</tbody>[/TABLE]

and here i want the value 1000

its regarding calculation customers credit limit for work
Click to expand...


so are you getting the desired value?
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Match, looking, insert. (two tables in respect to the date " monthly"
Replies
3
Views
392
Batata
B
L
  • Question Question
calculating credit limit with payment terms
Replies
4
Views
11K
LarsSJensen
L
Wimpie
  • Question Question
Text to Value
Replies
8
Views
848
gaz_chops
G
D
  • Question Question
Add blank row in data set IF no data exists
Replies
3
Views
679
davie_aus
D
P
  • Question Question
INDEX, multiple parameters help!
Replies
7
Views
723
bsquad
B

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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
Back
Top